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'

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'