Hello there,
I tried to calculate tickets and revenue info by using perfs time range and season number.
I created two scripts to do the same calculation and expect the same result.
One is using fileds in table T_LINEITEM,and another one is using fileds in t_sub_lineitem.
The result set is different.
In my first scripts, if I remove
and c.li_seq_no in (select li_seq_no from t_sub_lineitem where sli_status in (3, 12))
I could get the same result…
Any suggestions…
Thank you and have a good day!
USE [impresario]
GO
Declare @season_no int
Declare @perf_start_dt datetime
Declare @perf_end_dt datetime
Set @season_no ='104'
Set @perf_start_dt = '2009-4-1'
Set @perf_end_dt = '2009-4-30'
SELECT distinct i.description , sum(c.num_seats_pur) - sum(c.num_seats_ret) as 'Number of Tickets',
sum(c.tot_pur_amt) + sum(c.tot_ret_amt) as 'Revenue'
FROM T_CUSTOMER a
INNER JOIN T_ORDER b ON a.customer_no = b.customer_no
INNER JOIN T_LINEITEM c ON b.order_no = c.order_no
INNER JOIN T_PERF d ON c.perf_no = d.perf_no
INNER JOIN t_inventory i on i.inv_no = c.perf_no
WHERE
d.season = @season_no
and d.perf_dt >= @perf_start_dt
and d.perf_dt <= @perf_end_dt
and c.li_seq_no in (select li_seq_no from t_sub_lineitem where sli_status in (3, 12)) --remove this one, results are the same
group by i.description
select i.description, d.description, count(due_amt) as 'Number of Tickets', sum(c.due_amt) as 'Revenue'
from [dbo].t_order a
join t_lineitem b on a.order_no = b.order_no
join t_sub_lineitem c on b.li_seq_no = c.li_seq_no
join t_perf e on c.perf_no = e.perf_no
join t_prod_season f on e.prod_season_no = f.prod_season_no
join t_production g on f.prod_no = g.prod_no
join t_title h on g.title_no = h.title_no
join t_inventory d on h.title_no = d.inv_no
join t_facility i on e.facility_no = i.facil_no
where
f.season = @season_no
and c.sli_status in (3, 12)
and e.perf_dt >= @perf_start_dt
and e.perf_dt <= @perf_end_dt
group by i.description, d.description
Hi Vicky,
Good day.
Nice try.
1, t_lineitem is useless, so let it be.
2, you can count tickets at t_sub_lineitem level, but not the money.
3, you need to link t_sli_detail to count tickets income.
So what is the structure?
1, t_sub_lineitem have order_no, sli_no, it can link to t_order table, and t_sli_detail table.
2, t_sli_detail table have pmap_no, it can link to t_pmap then pick up the GL.
3, t_pmap have GL and price_category, so it can tell whether the item is a ticket or fee and so on.
then How to calculatecalculate?
1, every record in t_sli_detail can be regards as one ticket, if pmap_no tell you it is a ticket. how? price_category will tell you (price_category=1). so link t_sli_detail to t_pmap.
2, link t_sub_lineitem sli_status(3,12) to t_sli_detail, then add paid_amt field together in t_sli_detail table, you will have the ticket sales.
3, if you disregard sli_status, add paid_amt together by picking up the gl code from t_pmap, then you can balance finance GL.
4, at this point, you need to pick up batch_no from t_sub_lineitem table, and check out whether the batch is posted or not from t_batch table. only posted batches are in GL reports. I should say they are in t_gl_posting _history table.
select top 10 *from t_sli_detail
select top 10 *from t_sub_lineitem
select top 10 * from t_pmap
select * from t_gl_posting_history
have fun.
Ben
Hello Ben,
Thank you very much for your help .
Have a good day!
Vicky