SQLTeam.com | Weblogs | Forums

Pivot table problem


#1

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