Sample interest weighting procedure

Hi Kim,

We are in the same boat and I can't find the sample procedure anywhere. let me know if you were able to find it.

Thanks,

Kelly

Hi Kim,

We are in the same boat and I can't find the sample procedure anywhere. let me know if you were able to find it.

Thanks,

Kelly

The link appears to be broken, but I have a copy of it.

USE [impresario]
GO
/****** Object: StoredProcedure [dbo].[LP_INTERESTS_WEIGHT_SAMPLE] Script Date: 03/18/2009 11:29:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[LP_INTERESTS_WEIGHT_SAMPLE]

as

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

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

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

--create a temp table with the counts
select distinct o.customer_no, it.tkw, count(s.sli_no) weight, 'N' tkw_update --one count for each seat
into #tkw
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 order_dt > dateadd (yy,-1,getdate()) --orders within the last year
and s.due_amt > 0 --not comps
group by o.customer_no, it.tkw

--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'

Hello! I know it's been quite a while since this thread was active, but I am now attempting to set up this stored procedure but am running into errors in the final section for inserting new rows as seen here:

--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'

The results show the following error:

Msg 515, Level 16, State 2, Line 67
Cannot insert the value NULL into column 'last_update_dt', table 'impresario.dbo.TX_CUST_TKW'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Has anyone been able to find a way to fix this? With it being so old, it's possible an upgrade has changed the way it pulls this.  

Any help would be greatly appreciated!!

Dan Crowell

Hey Dan,  When I insert into TX_CUST_TKW I just do the following.  

Insert into TX_CUST_TKW (customer_no, tkw, selected)
select * from #TEMP_TABLE

The table will handle the rest. ie: tell the insert what columns you want to insert and leave out the last updates/createdate/location columns.

So maybe try

insert into tx_cust_tkw (customer_no, tkw, selected, weight)
select t.customer_no, t.tkw, t.tkw_update, t.weight
from #tkw t
where tkw_update = 'N'

and have a look at the results (if you get past the error - fingers crossed)

Hi Heath, Sorry I just noticed your reply. My notifications were turned off.  I made the edits like you suggested and it worked properly! However, it only worked the first time, any time after that I get the error "There is already an object named '#tkw' in the database.".  Can you clarify where you placed the top two lines you referenced (below) to use the Temp Table?

Insert into TX_CUST_TKW (customer_no, tkw, selected)
select * from #TEMP_TABLE

I assume it would be in that top portion of code (create a temp table with the counts)? Did you just replace the entire section (below) and replace with the two lines above?

---create a temp table with the counts
select distinct o.customer_no, it.tkw, count(s.sli_no) weight, 'N' tkw_update --one count for each seat
into #tkw
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 order_dt > dateadd (yy,-2,getdate()) --orders within the 2 last years
and s.due_amt > 0 --not comps
group by o.customer_no, it.tkw

I appreciate your help!!!

You can't INSERT customer/interest pairs that already exist.  You'll need to UPDATE those.  You may want to do this using the MERGE statement.

I'd advise against using seated paid as a standard for updating given the ticket record can be removed from the order. If the money is allocated to a different ticket then the constituent record will have overstated weights.

How do you handle returns for type 12 given the wide range of reasons tickets get returned? For example a ticket may get returned to be upgraded to a membership which means the sale should still count. However if it gets returned because the customer changed their mind about the product that should not count.

Lastly I'd want to have an audit table to know exactly whet values were added (or removed) and when. I'd then use the audit table to update the values on the attribute on the constituent record so that it would be visible in the application (although my preference would be to just have them pull the weight data from a report since it is mostly used for analytics anyway). The would then allow the removal of a record by either changing its status of inserting a record that would decrement the count if he ticket was returned.