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'.