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

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.

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.