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.