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]);