SQLTeam.com | Weblogs | Forums

Multiple pivots fields


#1

Hello,

I am having some challenges rendering the report attached below in SQL.

I would be greatful if someone can help me out there. See the sql scripts below. It does seems to get all the aggregates but not the sum of the ALL specialties and over all percentages of the categories ( grand total row.

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

Thank you for your help
ziggy