multiple pivot help

Question

multiple pivot help

ziggy2016

Valued Member

Points: 62
More actions
May 24, 2016 at 3:06 am

#314135

Hello,
I am wondering some can help me with the solution below.
1. I need to aggregate a report by YTD percentages and avg YTD with grand total row at the bottom. Please see the attached image. So far, I have managed to come up with the scripts below but, I need other bits that will generate the others as in the copy of the report as attached.
;WITH CTE_XXXXX AS(
SELECT [Treatment Function]
,CONVERT(varchar,SUM([Total within 18 weeks]),1) AS Total_Seen_WithIn_18wks
,CONVERT(varchar,SUM([Total_All]),1) AS YTD_Total_Known
,round(100 * SUM([Total within 18 weeks]),0) / NULLIF(SUM([Total_All]),0) AS [PCT_Seen_WithIn_18wks]
, FinMonth
,MonthNo
, [FinYear]AS FY
FROM [XXXX]. [dbo].[vw_XXXXXXXXXXSelect] with(nolock)
WHERE [Treatment Function] <> 'XXXXX'
AND RTT_Part_Description = 'XXXXXX'
and [Treatment Function] = 'ENT'
AND substring([Status],1,6) != 'SAVED,'
AND CCG_Code = 'XXXX'
GROUP BY [Treatment Function]
,MONTHNO
,FinMonth
,FinYear
,MonthNo
)
SELECT FY
, [Treatment Function]
,CONVERT(varchar,AVG(cast([YTD_Total_Known] as int)),1) AS AVG_Activity
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 1 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END)AS "APR"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 2 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END)AS "May"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 3 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END)AS "JUN"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 4 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END)AS "JUL"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 5 THEN ISNULL( [PCT_Seen_WithIn_18wks],0) END)AS "AUG"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 6 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END)AS "SEPT"
,MAX(CASE WHEN ISNULL( MONTHNo,0) = 7 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END)AS "OCT"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 8 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END)AS "NOV"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 9 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END)AS "DEC"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 10 THEN ISNULL( [PCT_Seen_WithIn_18wks],0) END)AS "JAN"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 11 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END)AS "FEB"
,MAX(CASE WHEN ISNULL(MONTHNo,0) = 12 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "MAR"
,CONVERT(varchar,AVG(ISNULL([PCT_Seen_WithIn_18wks],0) ),1) AS YTD
FROM CTE_XXXXX
GROUP BY [Treatment Function],FY

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Gave an answer in this other thread: http://www.sqlservercentral.com/Forums/Topic1789162-3077-1.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ziggy2016 Valued Member Points: 62 More actions · Answer 2

Thanks for your advice and timely reply.

Please see the sample of my dataset below and would happy if you help me with a pivot or grouping set you mentioned earlier on to generate the sample report I provided.

Thanks for your help

DECLARE @MyTable TABLE(

Period varchar(30)

, [MonthName] varchar(30)

, [Code] varchar(30)

, [Category] varchar(30)

, [Numerator] float

, [Denominator] float

, PCT_Nemerator float

, [Avg_Numerator] float

, YTD_numerator float

, GrandTotal_numerator float

);

INSERT INTO @MyTable

SELECT '2015-16','May','04Q','Cardiology',10,100,'','','','' UNION ALL

SELECT '2015-16','May','04Q','Cardiology',20,50,'','','','' UNION ALL

SELECT '2015-16','June','04Q','Cardiology',20,50 ,'','','','' UNION ALL

SELECT '2015-16','June','04Q','Cardiology',20,50,'','','','' UNION ALL

SELECT '2015-16','Jul','04Q','Cardiology',20,50,'','','','' UNION ALL

SELECT '2015-16','Jul','04Q','Cardiology',15,70,'','','','' UNION ALL

SELECT '2015-16','Aug','04Q','Urology',10,100,'','','','' UNION ALL

SELECT '2015-16','Aug','04Q','Cardiology',20,50 ,'','','','' UNION ALL

SELECT '2015-16','June','04Q','Urology',20,50 ,'','','','' UNION ALL

SELECT '2015-16','April','04Q','ENT',20,50,'','','','' UNION ALL

SELECT '2015-16','April','04Q','ENT',15,70,'','','','' UNION ALL

SELECT '2015-16','May','04Q','ENT',10,100 ,'','','','' UNION ALL

SELECT '2015-16','May','04Q','ENT',20,50,'','','','' UNION ALL

SELECT '2015-16','June','04Q','ENT',20,50,'','','','' UNION ALL

SELECT '2015-16','April','04Q','Cardiology',20,50,'','','','' UNION ALL

SELECT '2015-16','April','04Q','Cardiology',15,70,'','','',''

SELECT * FROM @MyTable

ziggy2016 Valued Member Points: 62 More actions · Answer 3