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

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.

Hi Gawain. Thank you for the input.  Unfortunately, I am not very familiar with formatting code on my own.  Would you be able/willing to lend a hand with updating that statement to update using the Merge statement like you mentioned? It would be greatly appreciated!

Haha!  I was hoping you wouldn't ask that.  I'm just starting to figure out MERGE myself (and very stupidly annihilated almost all of our Interests a few months ago because I included a DELETE clause without thinking it through -- we were able to recover them from a backup, thanks Support!)

Is it embarrassing to admit that I just never liked dealing with MERGE, so I just always UPDATE existing, DELETE those from my temp table and then INSERT the new ones?

I'm warming to it, but it is a sharp tool.

Also, is it one of the ones that bedevils intellisense?  Can't remember.

I cannot recall either.  I guess a part of me just always feels a little safer separating out the actions on their own.  And nothing I am doing with that is ever so database intensive (and usually is done at 3:00 AM) that it makes even the slightest difference in terms of operational capacity.

I do feel like once I become comfortable with it, it will also make what I'm doing easier for me to read in the future by pulling all the logic into one place.

And becoming more comfortable with and expanding your knowledge on a standard SQL process is rarely ever a bad thing.

Unless other people find out your skills and line up in front of your desk to do all their jobs.  That's why it's better to be SQL Batman and solve database crimes out from the shadows.

oooh a new toy.  I'll have a look at Merge and your error Dan Crowell and get back to you.  My memory is reminding me that I had the same error a couple of times