Hi all,
I am wondering if anybody would be willing to share with me a copy of your lp_customer_rank, specifically if you are assigning values to multiple rank types. I'm pretty much just looking for any example of one script that assigns multiple rank types values with different formulas for the different types.
Thanks.
I realise this is quite old, but I was about to post this exact question! We are exploring rankings and I would love to have some tips or examples from others who have done this!
We rank people by membership level. Would that help you?
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tracey Flattes Sent: Monday, September 23, 2013 4:31 PM To: Gloria Ormsby Subject: Re: [Tessitura Technical Forum] Share your ranking script?
From: Amanda Freeman <bounce-amandafreeman5080@tessituranetwork.com> Sent: 12/15/2011 3:53:45 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Hi Gloria,
It certainly would - right now we are at the beginning stages and I am a little overwhelmed by it all. Feel free to send me a private email - would love to hear what you do!
Here is my code which I don't "technically" have in LP_CUSTOMER_RANK because users wanted to manually manipulate some of the rankings. However, I think that this is a good usage for ranking since it creates a rank based on multiple data points: giving, ticket purchase, etc. One could just add an the insert statement to the end of this code to drop it into the ranking table. Please don't hesitate to contact me if there are questions:
DECLARE @tbl_25K table
(
customer_no int
)
--ADDED TO FILTER ENDOWMENTS GIFTS UNDER 250k
DECLARE @tbl_targets table
INSERT INTO @tbl_targets
SELECT
CUSTOMER_NO
FROM (SELECT
creditee_no customer_no
,ISNULL(SUM(cont_amt),0)cont_amt
FROM T_CUSTOMER CUS
JOIN T_CREDITEE TCRE
on CUS.customer_no = TCRE.creditee_no
JOIN T_CONTRIBUTION TCON
on TCRE.ref_no = TCON.ref_no
JOIN T_CAMPAIGN camp
on camp.campaign_no = tcon.campaign_no and category = 11
group by
creditee_no
UNION ALL
CUS6.customer_no
,ISNULL(SUM(cont_amt),0) cont_amt
FROM T_CUSTOMER CUS6
on CUS6.customer_no = TCON.customer_no
where TCON.ref_no NOT IN (SELECT ref_no FROM T_CREDITEE)
CUS6.customer_no) DATA
GROUP BY customer_no
HAVING SUM(cont_amt) >= 250000
--END COMMMENT
--25K qualifier
INSERT INTO @tbl_25K
distinct customer_no
from (select
customer_no
from T_CONTRIBUTION cont
join T_CAMPAIGN camp
on camp.campaign_no = cont.campaign_no and fyear = 2012
HAVING SUM(CONT_AMT) >= 25000
UNION
select
on camp.campaign_no = cont.campaign_no and fyear = 2013
HAVING SUM(CONT_AMT) >= 25000) corp
--OUTPUT
CUST.customer_no
,esal1_desc
,esal2_desc
,CUST.cust_type
,endow_amt = ISNULL((SELECT
ISNULL(SUM(CONT_AMT),0) CONT_AMT
WHERE DATA.CUSTOMER_NO = CUST.CUSTOMER_NO
HAVING SUM(cont_amt) >= 250000),0)
,'order_amt' = ISNULL((SELECT
ISNULL(SUM(TOR.tot_paid_amt - tor.tot_contribution_paid_amt),0) order_amt
FROM T_CUSTOMER CUS1
LEFT JOIN T_ORDER TOR
ON CUS1.customer_no = TOR.customer_no
JOIN T_APPEAL TA
ON TA.appeal_no = TOR.APPEAL_NO
JOIN T_CAMPAIGN TCAMP
ON TCAMP.campaign_no = TA.campaign_no and (fyear >= 2009 and fyear <=2014)
where CUS1.customer_no = cust.customer_no),0)
,'don_amt' = ISNULL((SELECT
ISNULL(SUM(recd_amt),0) amount
,ISNULL(SUM(RECD_amt),0) recd_amt
FROM T_CUSTOMER CUS3
on CUS3.customer_no = TCRE.creditee_no
on camp.campaign_no = tcon.campaign_no and category != 11
where (fyear >= 2009 and fyear <=2014)
CUS4.customer_no
,ISNULL(SUM(recd_amt),0) recd_amt
FROM T_CUSTOMER CUS4
on CUS4.customer_no = TCON.customer_no
where (fyear >= 2009 and fyear <=2014) and TCON.ref_no NOT IN (SELECT ref_no FROM T_CREDITEE)
--ADDED TO HANDLE ENDOWMENT GIFTS FOR PATRONS UNDER 250K
UNION All
where (fyear >= 2009 and fyear <=2014) AND creditee_no NOT IN (SELECT customer_no from @tbl_targets)
AND cus4.customer_no NOT IN (SELECT customer_no from @tbl_targets)
--END ADDITIONAL CODE
) DA
WHERE DA.CUSTOMER_NO = CUST.CUSTOMER_NO),0)
,'BOG' = CASE WHEN tcc.constituency IS NOT NULL THEN 'Y'
ELSE 'N'
END
,'Corp_Donor' = CASE WHEN CUST.cust_type NOT IN (1,7,9) AND CUST.customer_no IN (Select customer_no from @tbl_25K) THEN 'Y'
INTO ##OUTPUT_PLI
FROM T_CUSTOMER CUST
LEFT JOIN TX_CUST_SAL TCS
ON TCS.customer_no = CUST.customer_no AND TCS.default_ind = 'Y'
LEFT JOIN TX_CONST_CUST TCC
ON TCC.customer_no = CUST.customer_no AND constituency = 1 AND (end_dt >= GETDATE() or end_dt is null)
WHERE inactive = 1 AND cust_type != 6 AND CUST.customer_no not in (324110,181314,228393,486660,519662,527172,530559,523934)
rank() OVER (ORDER BY (ENDOW_AMT + ORDER_AMT + DON_AMT) DESC,CUSTOMER_NO ASC) as PLI
,CUSTOMER_NO
,ESAL1_DESC
,ESAL2_DESC
,CUST_TYPE
,ENDOW_AMT
,ORDER_AMT
,DON_AMT
,'TOTAL' = (ENDOW_AMT + ORDER_AMT + DON_AMT)
,BOG
,Corp_Donor
FROM ##OUTPUT_PLI
WHERE (ENDOW_AMT + ORDER_AMT + DON_AMT) > 0 OR BOG='Y'
AND CUSTOMER_NO NOT IN (select individual_customer_no from T_AFFILIATION where affiliation_type_id = 10002) --filter individuals that belong to HH
ORDER BY
PLI