May 24, 2016 at 3:37 am
Hi Guys,
I hope someone can help me resolve the problem below:
FYTreatment FunctionAVG_ActivityAPRMayJUNJULAUGSEPTOCTNOVDECJANFEBMARYTD
2015/16Other150191949494949393929393919192
2015/16Ophthalmology63994969494959798989797959796
2015/16Trauma & Orthopaedics100390918989929492919391929191
2015/16Neurosurgery329295951001009494919596979094
2015/16Rheumatology11895949798969792919392979895
Over All Total329386%?86%?86%?86%?86%?86%?86%?86%?86%?86%?86%?86%?86%?
Basically, all I want is how get the bottom over all avg and percentages as seem in the Over All Total row. See the attached. Below is the script I have written sofar. Thanks for your help
;WITH CTE_Specialty 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 [XXXXX]. [dbo].[vw_Monthly_ XXXXXXX_Select] with(nolock)
WHERE [Treatment Function] <> 'C_CCCC'
AND RTT_Part_Description = 'XXXXXXXX'
AND substring([Status],1,6) != 'SAVED,'
AND CCG_Code = '0XXXX'
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_Specialty
GROUP BY [Treatment Function],FY
May 24, 2016 at 7:21 am
ziggy2016 (5/24/2016)
Hi Guys,I hope someone can help me resolve the problem below:
FYTreatment FunctionAVG_ActivityAPRMayJUNJULAUGSEPTOCTNOVDECJANFEBMARYTD
2015/16Other150191949494949393929393919192
2015/16Ophthalmology63994969494959798989797959796
2015/16Trauma & Orthopaedics100390918989929492919391929191
2015/16Neurosurgery329295951001009494919596979094
2015/16Rheumatology11895949798969792919392979895
Over All Total329386%?86%?86%?86%?86%?86%?86%?86%?86%?86%?86%?86%?86%?
Basically, all I want is how get the bottom over all avg and percentages as seem in the Over All Total row. See the attached. Below is the script I have written sofar. Thanks for your help
;WITH CTE_Specialty 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 [GEM_Optum_Sandbox]. [rtt].[vw_Monthly_ CCG_Prov_RTT_Select] with(nolock)
WHERE [Treatment Function] <> 'C_CCCC'
AND RTT_Part_Description = 'XXXXXXXX'
AND substring([Status],1,6) != 'SAVED,'
AND CCG_Code = '0XXXX'
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_Specialty
GROUP BY [Treatment Function],FY
You have several code smells in there. Some of them are:
- Starting your code with a semicolon instead of ending each statement with it.
- Using functions in your WHERE clause
- Using NOLOCK hint, on a view. That might cause inaccurate results when least expected.
- Using ISNULL when it's not needed.
- Inconsistent formatting.
Here's how I would write the code myself, which certainly isn't a golden rule, but serves as example for my comments.
WITH CTE_Specialty 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 [GEM_Optum_Sandbox].[rtt].[vw_Monthly_ CCG_Prov_RTT_Select]
WHERE [Treatment Function] <> 'C_CCCC'
AND RTT_Part_Description = 'XXXXXXXX'
AND [Status] NOT LIKE 'SAVED,%'
AND CCG_Code = '0XXXX'
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 MONTHNo = 1 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "APR"
,MAX(CASE WHEN MONTHNo = 2 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "May"
,MAX(CASE WHEN MONTHNo = 3 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "JUN"
,MAX(CASE WHEN MONTHNo = 4 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "JUL"
,MAX(CASE WHEN MONTHNo = 5 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "AUG"
,MAX(CASE WHEN MONTHNo = 6 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "SEPT"
,MAX(CASE WHEN MONTHNo = 7 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "OCT"
,MAX(CASE WHEN MONTHNo = 8 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "NOV"
,MAX(CASE WHEN MONTHNo = 9 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "DEC"
,MAX(CASE WHEN MONTHNo = 10 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "JAN"
,MAX(CASE WHEN MONTHNo = 11 THEN ISNULL([PCT_Seen_WithIn_18wks],0) END) AS "FEB"
,MAX(CASE WHEN MONTHNo = 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_Specialty
GROUP BY [Treatment Function],FY;
Now, after the rant (sorry for that). To get your results, you can use ROLLUP or GROUPING SETS. I would give a proper example, but you didn't post consumable sample data. You can find information here: https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
By the way, this should be preferably generated in the presentation layer. That way, you'll have less trouble when trying to format your rows.
May 24, 2016 at 8:04 am
Luis Cazares (5/24/2016)
You have several code smells in there. Some of them are:- Using functions in your WHERE clause
Just to clarify... having functions in your WHERE clause isn't a problem. Having table columns encapsulated in function in you WHERE clause is.
However and as with everything else, "It Depends". For example, the encapsulation of a flag column in an ISNULL where you want to treat nulls as zeros (ISNULL(FlagCoumn,0)=0, for example) for comparison might still allow the query to be SARGable [font="Arial Black"]IF[/font] there are other predicates that will match a good index before it gets to the flag column. If the stars are in alignment between those other predicates and a good index, an Index Seek will still occur, although the range scan that follows that might be a little bigger (or a lot if the table, index, and query aren't designed correctly).
--Jeff Moden
Change is inevitable... Change for the better is not.
May 24, 2016 at 8:14 am
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','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','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
May 24, 2016 at 8:52 am
Here's an example.
The query is modified because I'm not sure that you're using the correct aggregates.
WITH CTE_Specialty AS(
SELECT Category AS [Treatment Function]
,SUM(Numerator) AS Total_Seen_WithIn_18wks
,SUM(Denominator) AS YTD_Total_Known
,round(100 * SUM(Numerator),0) / NULLIF(SUM(Denominator),0) AS [PCT_Seen_WithIn_18wks]
,MonthName
,Period AS FY
FROM @MyTable --[GEM_Optum_Sandbox].[rtt].[vw_Monthly_ CCG_Prov_RTT_Select]
--WHERE [Treatment Function] <> 'C_CCCC'
--AND RTT_Part_Description = 'XXXXXXXX'
--AND [Status] NOT LIKE 'SAVED,%'
--AND CCG_Code = '0XXXX'
GROUP BY Category
,MonthName
,Period
)
SELECT ISNULL( FY, 'All FY') AS FY
,ISNULL( [Treatment Function], 'All Specialties') AS [Treatment Function]
,AVG([YTD_Total_Known] ) AS AVG_Activity
,AVG(CASE WHEN MonthName = 'April' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "APR"
,AVG(CASE WHEN MonthName = 'May' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "May"
,AVG(CASE WHEN MonthName = 'JUNe' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "JUN"
,AVG(CASE WHEN MonthName = 'JUL' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "JUL"
,AVG(CASE WHEN MonthName = 'AUG' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "AUG"
,AVG(CASE WHEN MonthName = 'SEPT' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "SEPT"
,AVG(CASE WHEN MonthName = 'OCT' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "OCT"
,AVG(CASE WHEN MonthName = 'NOV' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "NOV"
,AVG(CASE WHEN MonthName = 'DEC' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "DEC"
,AVG(CASE WHEN MonthName = 'JAN' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "JAN"
,AVG(CASE WHEN MonthName = 'FEB' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "FEB"
,AVG(CASE WHEN MonthName = 'MAR' THEN ISNULL([PCT_Seen_WithIn_18wks],0) ELSE 0 END) AS "MAR"
,AVG(ISNULL([PCT_Seen_WithIn_18wks],0) ) AS YTD
FROM CTE_Specialty
GROUP BY ROLLUP( FY, [Treatment Function]);
I want to thank Jeff for clarifying what I erroneously worded. Most of the things I mentioned, are not exactly problems, but could become problems under certain circumstances.
May 24, 2016 at 9:10 am
Many thanks to you and others who have contributed sofar. In the Month column, it displays avg but what I want is the percentages like 90% and so on. Basically, athe first column display Avg and months with percentages. Is this achievable.
Thanks
Ralph
May 24, 2016 at 9:14 am
ziggy2016 (5/24/2016)
Many thanks to you and others who have contributed sofar. In the Month column, it displays avg but what I want is the percentages like 90% and so on. Basically, athe first column display Avg and months with percentages. Is this achievable.Thanks
Ralph
Post expected results based on the sample data you posted. I'm not sure what you're up to.
May 24, 2016 at 9:20 am
Please see the attached image/sample report
May 24, 2016 at 9:23 am
That doesn't reflect the sample data you posted.
May 24, 2016 at 9:27 am
Hi Luis,
Thanks for the help.Maybe is me not explaining myself very well. However, that is exactly what I need as in the sample report. Can you please help me to achieve that or point me in some direction.
Thanks once again and I appreciate.
May 24, 2016 at 9:41 am
I understand that you want that format. But the image you posted, has more data that the sample data you posted. I have no idea of what the numbers should be. If you post the expected results based on your sample data, I can compare results with my solutions.
May 24, 2016 at 10:15 am
Hi Luis,
Thanks for all the troubles. Again here is my scripts. can this be used to achieved the sample report I sent earlier on. Do I need cross join to do that.
DECLARE @MyTable TABLE(
Period varchar(30)
, [MonthName] varchar(30)
, [Code] varchar(30)
, [Category] varchar(30)
, [breached] float
, [total] 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','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','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 UNION ALL
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','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','Dec','04Q','xxxxxx',10,100 UNION ALL
SELECT '2015-16','Dec','04Q','xxxxxx',20,50 UNION ALL
SELECT '2015-16','Dec','04Q','xxxxxx',20,50 UNION ALL
SELECT '2015-16','Jan','04Q','xxxxxx',20,50 UNION ALL
SELECT '2015-16','Jan','04Q','Cardiology',20,50 UNION ALL
SELECT '2015-16','Jan','04Q','Cardiology',15,70
;WITH CTE_Spec AS (
SELECT [Category]
,SUM([breached]) AS Total_Breached
,SUM([Total]) AS YTD_Total
, ROUND((CAST(SUM(breached) AS FLOAT)/CAST(SUM(Total) AS FLOAT))*100, 2) AS PCT_Breached
,Period
,[MonthName]
FROM @MyTable
GROUP BY [Category]
,Period
,[MonthName]
)
SELECT [Category]
,CONVERT(varchar,AVG(cast([Total_Breached] as int)),1) AS AVG_Activity
,MAX(CASE WHEN [MonthName] = 'April' THEN ISNULL([PCT_Breached],0) END)AS '1'
,MAX(CASE WHEN [MonthName] = 'May' THEN ISNULL([PCT_Breached],0) END)AS '2'
,MAX(CASE WHEN [MonthName] = 'June' THEN ISNULL([PCT_Breached],0) END)AS '3'
,MAX(CASE WHEN [MonthName] = 'July' THEN ISNULL([PCT_Breached],0) END)AS '4'
,MAX(CASE WHEN [MonthName] = 'August' THEN ISNULL([PCT_Breached],0) END) AS '5'
,MAX(CASE WHEN [MonthName] = 'Dec' THEN ISNULL([PCT_Breached],0) END)AS '9'
,MAX(CASE WHEN [MonthName] = 'Jan' THEN ISNULL([PCT_Breached],0) END)AS '12'
----- and so on
,AVG(ISNULL([PCT_Breached],0) ) AS YTD
FROM CTE_Spec
GROUP BY [Category]
--GROUP BY ROLLUP( [Category]);
May 26, 2016 at 4:58 pm
Are you doing this in SSRS? Use a Matrix... does the pivot for you.
May 27, 2016 at 7:43 am
Hi
many thanks for your help. There is no SSRS, hence I am trying to do it in T-SQL so that they can run it and export to excel.
Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply