• Replies 6 replies
  • Subscribers 337 subscribers
  • Views 125 views
  • Users 0 members are here
Related

Ticket History Madness

David Crowell
$organization

What started as a simple (?!) task to add two new fields to our t_tck_hist table, has taken an ugly turn.  The LP_UPDATE_TKT_HIST procedure is nearly unreadable spaghetti.  I requested and recieved a fresh copy.  It looks nearly the same as the spaghetti, with slightly better formatting.

Has anyone re-written their ticket history updating procedure to be more clean, compact, readable, and hopefully eliminated the cursors?

If I just have a query to pull all ticket history for a season, I can work with that.  The fields I need are:

customer_no
sale_type
performance_dt
matinee_or_evening
tck_amt
tck_type
price_type
num_seats
performance_name
location
season
mos_category
mos
order_dt

Thanks!

 

Parents Reply Children
  • $organization in reply to David Crowell

    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

     

  • $organization in reply to David Crowell

    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