• Replies 1 reply
  • Subscribers 336 subscribers
  • Views 245 views
  • Users 0 members are here
Related

import / update interests

Former Member
Former Member $organization

i'm hoping for an easier softer way...

We've been using interests in tessitura since close to our beginnings, which is only 4 months, but weight interests on a 1 ticket to 1 point scale.

i'd now like to get around to applying weight to the ticket history i imported!

Naturally, i'll need to do some fancy footwork to get the info even close to what the conversion doc says for TX_CUST_TKW

But even if i did, the BCP utility doesn't want to update the data, as i get a duplicate key error if there is already a value...

So Plan B;  import it in the same format to a staging table, then write my own update sql...

thought C:

Could i make a table say ltx_tkt_hist_tkw with ltx_tkt_hist_tkw.event and ltx_tkt_hist_tkw.interest_ID

then run a procedure for each row/ticket in LT_TKT_HIST before we lauched interests where event_desc =  ltx_tkt_hist_tkw.event, update tx_cust_tkw with 1 weight where interest = ltx_tkt_hist_tkw.interest_ID

~~~~~

Thought C came to me as i was posting this....other thoughts or opinions are welcome.  i'll let you know if i come up with a solution!

 

Parents
  • Former Member
    Former Member $organization

    So, I worked it out.

    I first went back to my LP_INTERESTS_WEIGHTED proc, and noticed that it wipes all interests and recalculates all interests nightly, based on t_order data.  But my last thought was on the right track, i just needed to associate an interest (tkw) to events prior to our implementation.

    I made a cross reference table to join perf_names to ticket history and associate the particular interest for that event.

    then I updated my Proc to UNION ALL and Sum the weight of equal interests.  

    Once i fully populate my LTX table, i'll be able to do some more thorough testing, but a quick test seems like it is all summing as expected.

    here is the SQL:

    /******************************************************************************************

    Created by Anna E. Wessely - Tessitura Network
    2009-03-18 
    as SAMPLE
    Procedure updates interests on the constituent record
    gives one point of weight for every ticket sold in the last year except for tickets with a due amount = 0
    Interests are cross-referenced with keywords which are at all levels - Title, Production, Prod Season, and Perf
    ---updated by JEB 2/18/11---
    added a custom cross reference table to join imported ticket history with no orders.  altered the select statement to union and sum the weight
    *******************************************************************************************/
    --create a temp table with the counts
    select customer_no, tkw, sum(weight) weight, tkw_update
    into #tkw
    from
    (select distinct o.customer_no, it.tkw, 'N' as tkw_update, count(s.sli_no) weight --one count for each seat
    from t_order o 
    join t_sub_lineitem s on s.order_no = o.order_no
    join t_perf p on p.perf_no = s.perf_no
    join t_prod_season ps on ps.prod_season_no = p.prod_season_no
    join t_production pn on pn.prod_no = ps.prod_no
    join tx_inv_tkw it on (it.inv_no = p.perf_no) or (it.inv_no = ps.prod_season_no) or (it.inv_no = pn.prod_no) or (it.inv_no = pn.title_no)
    where s.sli_status in (3,12,13) --seated paid, ticketed, donated
    and s.due_amt > 0 --not comps
    group by o.customer_no, it.tkw
    UNION ALL
    SELECT a.customer_no, (SELECT DISTINCT b.interest) as tkw, 'N' as tkw_update, SUM(a.num_seats) weight
    FROM  LTX_TKT_KWD b JOIN
                   LT_TKT_HIST a ON b.event = a.perf_name
    GROUP BY a.customer_no, b.interest, a.num_seats) as w2
    group by customer_no, tkw, tkw_update
    --update the table to note the rows which are already in the final table
    update #tkw
    set tkw_update = 'Y' 
    from #tkw t
    join tx_cust_tkw ct on ct.customer_no = t.customer_no and ct.tkw = t.tkw
    --update all weights to 0
    update tx_cust_tkw
    set weight = 0
    --update rows already in the table
    update tx_cust_tkw
    set weight = t.weight
    from #tkw t
    join tx_cust_tkw ct on ct.customer_no = t.customer_no and ct.tkw = t.tkw
    where t.tkw_update = 'Y'
    --insert new rows for any which are not in the table
    insert into tx_cust_tkw
    select t.customer_no, t.tkw, 'N', t.weight, substring(@@servername,1,16),'PROCESS', getdate(), null, null
    from #tkw t
    where tkw_update = 'N'

Reply
  • Former Member
    Former Member $organization

    So, I worked it out.

    I first went back to my LP_INTERESTS_WEIGHTED proc, and noticed that it wipes all interests and recalculates all interests nightly, based on t_order data.  But my last thought was on the right track, i just needed to associate an interest (tkw) to events prior to our implementation.

    I made a cross reference table to join perf_names to ticket history and associate the particular interest for that event.

    then I updated my Proc to UNION ALL and Sum the weight of equal interests.  

    Once i fully populate my LTX table, i'll be able to do some more thorough testing, but a quick test seems like it is all summing as expected.

    here is the SQL:

    /******************************************************************************************

    Created by Anna E. Wessely - Tessitura Network
    2009-03-18 
    as SAMPLE
    Procedure updates interests on the constituent record
    gives one point of weight for every ticket sold in the last year except for tickets with a due amount = 0
    Interests are cross-referenced with keywords which are at all levels - Title, Production, Prod Season, and Perf
    ---updated by JEB 2/18/11---
    added a custom cross reference table to join imported ticket history with no orders.  altered the select statement to union and sum the weight
    *******************************************************************************************/
    --create a temp table with the counts
    select customer_no, tkw, sum(weight) weight, tkw_update
    into #tkw
    from
    (select distinct o.customer_no, it.tkw, 'N' as tkw_update, count(s.sli_no) weight --one count for each seat
    from t_order o 
    join t_sub_lineitem s on s.order_no = o.order_no
    join t_perf p on p.perf_no = s.perf_no
    join t_prod_season ps on ps.prod_season_no = p.prod_season_no
    join t_production pn on pn.prod_no = ps.prod_no
    join tx_inv_tkw it on (it.inv_no = p.perf_no) or (it.inv_no = ps.prod_season_no) or (it.inv_no = pn.prod_no) or (it.inv_no = pn.title_no)
    where s.sli_status in (3,12,13) --seated paid, ticketed, donated
    and s.due_amt > 0 --not comps
    group by o.customer_no, it.tkw
    UNION ALL
    SELECT a.customer_no, (SELECT DISTINCT b.interest) as tkw, 'N' as tkw_update, SUM(a.num_seats) weight
    FROM  LTX_TKT_KWD b JOIN
                   LT_TKT_HIST a ON b.event = a.perf_name
    GROUP BY a.customer_no, b.interest, a.num_seats) as w2
    group by customer_no, tkw, tkw_update
    --update the table to note the rows which are already in the final table
    update #tkw
    set tkw_update = 'Y' 
    from #tkw t
    join tx_cust_tkw ct on ct.customer_no = t.customer_no and ct.tkw = t.tkw
    --update all weights to 0
    update tx_cust_tkw
    set weight = 0
    --update rows already in the table
    update tx_cust_tkw
    set weight = t.weight
    from #tkw t
    join tx_cust_tkw ct on ct.customer_no = t.customer_no and ct.tkw = t.tkw
    where t.tkw_update = 'Y'
    --insert new rows for any which are not in the table
    insert into tx_cust_tkw
    select t.customer_no, t.tkw, 'N', t.weight, substring(@@servername,1,16),'PROCESS', getdate(), null, null
    from #tkw t
    where tkw_update = 'N'

Children
No Data