Hi
I am struggling with the above pivot table SQL above. Basically, all I want to do is to add all categories sum of AVG and the overall percentages at the bottom of the table. I have managed to get the first part(the aggreagte data) but, the overall total avg and percentages I have no clue.
See my SQL script below:
Declare @fY AS varchar(25)
---,@Month AS varchar(25)
,@Spec varchar(50)
,@RTT_Part_Desc varchar(255)
--,@CCG varchar(255)
SET @fY = '2015/16'
---SET @Month = '11'
SET @spec = 'ENT'
SET @RTT_Part_Desc = 'Incomplete Pathways'
;WITH CTE_Base_Data AS (
SELECT [Treatment Function] AS Specialty
, [CCG_Name]
, [CCG_Code]
, SUM([Total within 18 weeks]) AS [Total within 18 weeks]
, SUM([Total_All]) AS Total_Patients_On_Incomplete_Pathways
, AVG(cast([Total within 18 weeks] as int)) AS AvgVal
, Round((cast(sum([Total within 18 weeks]) as float)/cast(NULLIF(sum([Total_All]),0)as float))*100,1) AS Pct_Within_18_Weeks
, [FinMonth]
, MonthNo
, [FinYear]
FROM [GEM_Optum_Sandbox]. [rtt].[vw_Monthly_ CCG_Prov_RTT_Select] AS rtt
LEFT JOIN [GEM_Optum_Sandbox].[rtt].[Prov_Code] AS pc
ON rtt. Provider_Code = PC.ProvCode
LEFT JOIN [GEM_Optum_Sandbox].[rtt].[Comm_Code] AS CG
ON rtt .CCG_Code = CG.[Code]
---WHERE [Treatment Function] <> 'XXXXX'
--AND CCG_Code IN('XXX','XXXX','XXXXX','XXXXX')
--AND substring([Status],1,6) != 'SAVED,'
--AND FinYear = @fY
--AND RTT_Part_Description = @RTT_Part_Desc
--AND [Treatment Function] = @spec
GROUP BY [Treatment Function], [CCG_Name], [CCG_Code], [FinMonth] , MonthNo, [FinYear]
)
SELECT CCG_Name
, Specialty
,CONVERT(varchar,AVG(cast(Total_Patients_On_Incomplete_Pathways as int)),1) AS AVG_Wait
,MAX(CASE WHEN ISNULL(MonthNo,0) = 1 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "APR"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 2 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "May"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 3 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "JUN"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 4 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "JUL"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 5 THEN ISNULL( [Pct_Within_18_Weeks],0) END) AS "AUG"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 6 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "SEPT"
,MAX(CASE WHEN ISNULL( MonthNo,0) = 7 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "OCT"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 8 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "NOV"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 9 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "DEC"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 10 THEN ISNULL( [Pct_Within_18_Weeks],0) END) AS "JAN"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 11 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "FEB"
,MAX(CASE WHEN ISNULL(MonthNo,0) = 12 THEN ISNULL([Pct_Within_18_Weeks],0) END) AS "MAR"
, AVG(ISNULL(Pct_Within_18_Weeks,0) ) AS YTD
FROM CTE_Base_Data
GROUP BY CCG_Name , Specialty
Thanks for your help
Ziggy
