Calculating Interest Weighting to consider Recipients

This is a really old thread but I was wondering this as well and adjusted the sample I found on another thread to use Ticket History:

USE [impresario]
GO

/****** Object:  StoredProcedure [dbo].[LP_INTERESTS_WEIGHT]    Script Date: 10/6/2021 9:35:01 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE procedure [dbo].[LP_INTERESTS_WEIGHT]

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

ZGD 9/30/2021 - adjusting/testing for use at The Phoenix Theatre Company
	Adjusted insert statement to not try and insert null values to update columns as per https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_technical-9/19094/sample-interest-weighting-procedure/74803
	Adjusted to last 5 years
	TNEW_SHORTREG_INTERESTS only
	SLI Status just ticketed and seated paid
	Not exclude comps

	10/5/21 - updated count(s.sli_no) to count(DISTINCT s.perf_no) to assign one count per performance rather than ticket
	10/6/21 - removed T_ORDER and T_SUB_LINEITEMS and replaced with T_TICKET_HISTORY - copy of previous first section:

			--create a temp table with the counts
			select distinct o.customer_no, it.tkw, count(DISTINCT s.perf_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)
			join TR_TKW t ON t.id = it.tkw
			where s.sli_status in (3,12) --seated paid, ticketed
			and order_dt > dateadd (yy,-5,getdate()) --orders within the 5 last years
			--and s.due_amt > 0 --not comps
			AND t.category = 7 -- TNEW_SHORTREG_INTERESTS only

			group by o.customer_no, it.tkw

	Added filter for role (OIR):
			1=owner
			2=initiator --excluded
			4=recipient
			3=owner/initiator
			5=owner/recipient
			6=initiator/recipient
			7=owner/initiator/recipient 


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


--create a temp table with the counts
select distinct h.customer_no, it.tkw, count(DISTINCT h.perf_no) weight, 'N' tkw_update --one count for each seat
into #tkw
from T_TICKET_HISTORY h
join t_perf p on p.perf_no = h.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)
join TR_TKW t ON t.id = it.tkw
where h.order_dt > dateadd (yy,-5,getdate()) --orders within the 5 last years
AND t.category = 7 -- TNEW_SHORTREG_INTERESTS only
AND h.Role IN(1,4,3,5,6,7) -- exclude just initiator, all others included

group by h.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 (customer_no, tkw, selected, weight)
select t.customer_no, t.tkw, t.tkw_update, t.weight
from #tkw t
where tkw_update = 'N'
GO

I made a few other adjustments including removing the no comps line, adjusting so each instance of a performance counts as one instead of each ticket, and to include just one interest category instead of all. All noted in the comment section.

Thread with original sample can be found here: community.tessituranetwork.com/.../sample-interest-weighting-procedure

This is nice.  I might have a go at using TX_PERF_SEAT to count the cancelled perfs as well.

Thanks for the discovery

This is nice.  I might have a go at using TX_PERF_SEAT to count the cancelled perfs as well.

Thanks for the discovery