Ticket History Madness

I appreciate the help here.  This gives me something to work with.  I can't see removing the location field without getting some heat about it.  I'll see if I can refactor that into a function.

Thanks!

As a follow-up, how do I modify this to get subscription history also?

I have ditched that pesky location field for now.  I'll add it back with a function, if forced to.  :)

I'm using the following (based on Ben's code) for ticket history:

select distinct
    o.customer_no,
    case when datepart(hh, perf.perf_dt) < 16 then 'M' else 'E' end as m_e,
    o.mos,
    o.order_dt,
    sli.perf_no,
    sli.seat_no,
    pt.short_desc as price_type,
    sli.due_amt,
    perf.perf_dt,
    perf.perf_code,
    perf.season,
    mos.category as mos_cat
into
    #dactmp
from
    t_order o
inner join
    T_SUB_LINEITEM sli on o.order_no = sli.order_no
inner join
    T_PERF perf on sli.perf_no = perf.perf_no
inner join
    TR_MOS mos on o.MOS = mos.id
inner join
    tr_price_type pt on pt.id = sli.price_type
where
    sli.sli_status in (3, 12)
    and perf.season = @season
    --and o.customer_no = 126075

   
select
    customer_no,
    season,
    perf_code,
    mos_cat,
    mos,
    perf_dt,
    m_e,
    sum(due_amt) as order_amt,
    price_type,
    count(*) as num_seats,
    order_dt
from
    #dactmp
group by
    customer_no,
    perf_dt,
    m_e,
    perf_code,   
    season,
    mos,
    mos_cat,
    price_type,
    order_dt
   
drop table #dactmp

As a follow-up, how do I modify this to get subscription history also?

I have ditched that pesky location field for now.  I'll add it back with a function, if forced to.  :)

I'm using the following (based on Ben's code) for ticket history:

select distinct
    o.customer_no,
    case when datepart(hh, perf.perf_dt) < 16 then 'M' else 'E' end as m_e,
    o.mos,
    o.order_dt,
    sli.perf_no,
    sli.seat_no,
    pt.short_desc as price_type,
    sli.due_amt,
    perf.perf_dt,
    perf.perf_code,
    perf.season,
    mos.category as mos_cat
into
    #dactmp
from
    t_order o
inner join
    T_SUB_LINEITEM sli on o.order_no = sli.order_no
inner join
    T_PERF perf on sli.perf_no = perf.perf_no
inner join
    TR_MOS mos on o.MOS = mos.id
inner join
    tr_price_type pt on pt.id = sli.price_type
where
    sli.sli_status in (3, 12)
    and perf.season = @season
    --and o.customer_no = 126075

   
select
    customer_no,
    season,
    perf_code,
    mos_cat,
    mos,
    perf_dt,
    m_e,
    sum(due_amt) as order_amt,
    price_type,
    count(*) as num_seats,
    order_dt
from
    #dactmp
group by
    customer_no,
    perf_dt,
    m_e,
    perf_code,   
    season,
    mos,
    mos_cat,
    price_type,
    order_dt
   
drop table #dactmp

Hi David,

Good day.

For subscription tickets, you just need to add pkg_no in the #dactmp table.

if you go with the easy way, just filter records with (pkg_no>0).

if you dig it deeper, you need to create a lookup table combine price_type and pkg_no, then you make the decision based on price_type and pkg_no (maybe also season).

select pkg_no, price_type, season from #dactmp  group by pkg_no, price_type, season

you need make the choices: which one is sub ticket, which not.

For location string, I attached a function, modified from FS_CONST_STRING.

other topics,

1, I saw you removed t_sli_detail, but if you pick the pmap_no from t_sli_detail, it will lead you to gl_hold_code. you can try to balance the gl report from here.

2, if you saved yesterday's #dactmp sales A, compare it with today's #dactmp sales B.

daily sales C= B-A. ( there is no order_dt or payment_dt confusion)

you have detail table, you can do anything. 

have fun

Ben