Hi all,
I hope everyone is staying safe and hopefully optimistic right now.
Myself and a colleague are working on code to pull out several functions from our gift officer's portfolio screens including "Natural Partners" aka a type of Plan Worker used to denote Board/Senior Stakeholder relationships with prospects.
In the System Tables the code reads: Select DISTINCT STUFF((Select DISTINCT ', '+worker_display_name FROM BI.VT_Plan_Worker WHERE worker_role_no=4 and plan_no=@plan_no For XML PATH ('')),1,1,'') FROM BI.VT_PLAN_WORKER where plan_no=@plan_no AND worker_role_no=4
Referencing the plan_no reflexively is something we're having difficulty with within the STUFF statement. So far I have:
USE impresario
SELECT DISTINCT plan_no, STUFF((Select DISTINCT ', '+convert(varchar (10),customer_no) FROM TX_CUST_PLAN WHERE role_no=4 For XML PATH ('')),1,1,'') FROM TX_CUST_PLAN WHERE role_no=4
Any ideas?
Thank you!Caro
Seems like you 95% there! Just need to grab the names by joining the VS_WORKER_LIST view. Would this work for you?
SELECT DISTINCTSTUFF((SELECT DISTINCT ', '+wl.worker_nameFROM TX_CUST_PLAN cp JOIN vs_worker_list wl ON wl.worker_customer_no = cp.customer_noWHERE role_no=4 AND plan_no = @plan_nofor XML PATH ('')),1,1,'')FROM TX_CUST_PLAN WHERE role_no=4
Thanks, Jason! Sorry for the delay in replying, I found that this didn't work because of the @plan_no. In the System Table it can work reflexively, but in this query I don't know how to include it.
LEFT JOIN (Select DISTINCT tp.customer_no, ct1.plan_no,
(SELECT STUFF(
(SELECT ', ' + ISNULL(b.fname+' '+b.lname,b.lname)
FROM TX_CUST_PLAN AS CT2
JOIN T_CUSTOMER AS b on b.customer_no = CT2.customer_no
WHERE CT2.plan_no = CT1.plan_no AND CT2.role_no = 4
FOR XML PATH(''), ROOT('root'), TYPE
) .value('/root[1]','VARCHAR(MAX)'),1,1,'')) AS NaturalPartners
FROM TX_CUST_PLAN AS CT1
LEFT JOIN T_PLAN AS TP ON tp.plan_no = ct1.plan_no
WHERE role_no = 4) AS D on d.customer_no = a.customer_no