Ticket History Madness

Hi David,

Good day.

results are tighten up with methods.

The fields you want to have most likely are decided by the methods you are using.

So if you want the same fields, I think maybe you have to take spaghetti.

like location field, you have to use a spaghetti way to create it.

but the question is why you need such a field?

So I attched some code to create your own sales detail table,then you can make your own sandwich.

have fun.

Ben

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

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