Pivot with constraints - UNION or other type of operator?

Hi

I am using MS SQL 2012.

Our clients get referrals to more than one team, and I have been asked to give a table showing (by client) the referrals to each team, but only those where they also have a referral to 'Healthy Lifestyles' (ppr.ReferralTeamID =32).

I can do the pivot for the query without the last constraint (Healthy Lifestyles) but cannot get it to work with the constraint. I thought I needed to do a UNION but that doesn't seem to work, and then I thought I need to include a Count > 1 of Referrals from one cte, but I can't get that to work either.

Hopefully someone can help.

This is my first pivot, that has all the referrals (I have commented out the Names to share the Results with you).

--Pivot showing open referrals by client
--All Referrals, including those without Healthy Lifestyle Referrals
select top (10) *
from 
(select 
	ppr.SEQNO as [Referral ID]
	,pd.NHI_NUMBER as [NHI No]
	--,(pd.FIRST_NAME + ' ' + pd.FAMILY_NAME) as [Full Name]
	,ppregions.RegionName as [Region Name]
FROM
	patient_details as pd
	full join patient_primhd_referral as ppr
	on pd.Patient_ID=ppr.PATIENT_ID
	full join PATIENT_ETH_TYPES as pet
	on pet.SEQNO=pd.ETHNICITY
	full join PATIENT_PRIMHD_TEAM_DETAILS as pptd
	on ppr.ReferralTeamID=pptd.SEQNO
	full join PATIENT_PRIMHD_REGIONS AS ppregions
	on ppregions.RegionID = pptd.RegionID
WHERE
	ReferralEndCodeID = 0
	and pd.NHI_NUMBER <> ''
) as a
pivot(count([Referral ID]) FOR [Region Name] IN (
			[Healthy Lifestyles]
			,[SCS]
			,[Luck Venue]
			,[804 Main Street]
			,[MHA PN]
			,[MH Palmerston North]
			,[MHA Hawkes Bay]
			,[Te Matai]
			,[MH Bluegum Road]
			,[MH Wellington]
			)) AS p
ORDER BY
	[NHI No]

This produces these results:

And this is my attempt at showing only those people with Healthy Lifestyles as one of the referrals:

--Pivot showing open referrals by client
--Only those including Healthy Lifestyle Referrals
--This doesn't work
select * 
from 
(select * from
(select
	ppr.SEQNO as [Referral ID]
	,Count (ppr.SEQNO) as [Count ID]
	,pd.NHI_NUMBER as [NHI No]
	--,(pd.FIRST_NAME + ' ' + pd.FAMILY_NAME) as [Full Name]
	,ppregions.RegionName as [Region Name]
FROM
	patient_details as pd
	full join patient_primhd_referral as ppr
	on pd.Patient_ID=ppr.PATIENT_ID
	full join PATIENT_ETH_TYPES as pet
	on pet.SEQNO=pd.ETHNICITY
	full join PATIENT_PRIMHD_TEAM_DETAILS as pptd
	on ppr.ReferralTeamID=pptd.SEQNO
	full join PATIENT_PRIMHD_REGIONS AS ppregions
	on ppregions.RegionID = pptd.RegionID
WHERE
	ReferralEndCodeID = 0
	and pd.NHI_NUMBER <> ''
	and ppr.ReferralTeamID =32
	and Count (ppr.SEQNO)  > 0) as a
union
(select 
	ppr.SEQNO as [Referral ID]
	,pd.NHI_NUMBER as [NHI No]
	--,(pd.FIRST_NAME + ' ' + pd.FAMILY_NAME) as [Full Name]
	,ppregions.RegionName as [Region Name]
FROM
	patient_details as pd
	full join patient_primhd_referral as ppr
	on pd.Patient_ID=ppr.PATIENT_ID
	full join PATIENT_ETH_TYPES as pet
	on pet.SEQNO=pd.ETHNICITY
	full join PATIENT_PRIMHD_TEAM_DETAILS as pptd
	on ppr.ReferralTeamID=pptd.SEQNO
	full join PATIENT_PRIMHD_REGIONS AS ppregions
	on ppregions.RegionID = pptd.RegionID
WHERE
	ReferralEndCodeID = 0
	and pd.NHI_NUMBER <> ''
) as b
pivot(count([Referral ID]) FOR [Region Name] IN (
			[Healthy Lifestyles]
			,[SCS]
			,[Luck Venue]
			,[804 Main Street]
			,[MHA PN]
			,[MH Palmerston North]
			,[MHA Hawkes Bay]
			,[Te Matai]
			,[MH Bluegum Road]
			,[MH Wellington]
			)) AS p
ORDER BY
	[NHI No]

I get this error, referring to the line starting with ) as b:

Msg 156, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'as'.

Based on your first/working query, try adding this after line: and pd.NHI_NUMBER <> ''

and exists (select 1
              from patient_primhd_referral as ppr2
             where ppr2.PATIENT_ID=ppr.PATIENT_ID
               and ppr2.ReferralTeamID=32
           )
1 Like

Thanks! I didn't realise I could use the table a second time by renaming it, I have learned a valuable skill

:smiley:

I now need to get a Sum of these pivot tables, so I can say how many referrals are for each service, and I still haven't figured out how to do ROLLUP, do you have any suggestions?