aggregation in different level of granularity

Question

For the table above...

what is the best method to get the view as follows...  is it the only way is to use the 2 sub queries? if so the perfomance is the problem right?

Answer 1

No, there is another.

Elimnating a subquery by using a common table expression is a great way to improve performance.

--SELECT
--	 Qtr		= 'Q1'
--	,Mth		= 2
--	,Wk			= 1
--	,NoOfDays	= 6
--	,DaysMTD	= 6
--	,DaysQTD	= 6
--INTO tmpMSDN;	

--INSERT INTO tmpMSDN
--	SELECT
--		 Qtr		= 'Q1'
--		,Mth		= 2
--		,Wk			= 2
--		,NoOfDays	= 7
--		,DaysMTD	= 13
--		,DaysQTD	= 13;

--INSERT INTO tmpMSDN
--	SELECT
--		 Qtr		= 'Q1'
--		,Mth		= 2
--		,Wk			= 3
--		,NoOfDays	= 7
--		,DaysMTD	= 20
--		,DaysQTD	= 20;
		
--INSERT INTO tmpMSDN
--	SELECT
--		 Qtr		= 'Q1'
--		,Mth		= 2
--		,Wk			= 4
--		,NoOfDays	= 6
--		,DaysMTD	= 26
--		,DaysQTD	= 26;			
		
		
		
--INSERT INTO tmpMSDN
--	SELECT
--		 Qtr		= 'Q1'
--		,Mth		= 3
--		,Wk			= 5
--		,NoOfDays	= 7
--		,DaysMTD	= 7
--		,DaysQTD	= 33;		
		
				
--INSERT INTO tmpMSDN
--	SELECT
--		 Qtr		= 'Q2'
--		,Mth		= 4
--		,Wk			= 15
--		,NoOfDays	= 7
--		,DaysMTD	= 7
--		,DaysQTD	= 7;	


WITH CTE1 AS
(
	SELECT 
	   f.[Qtr]
      ,f.[Mth]
      ,MaxMtD = MAX(DaysMTD)
      ,q.MaxQtd
	FROM tmpMSDN f
	JOIN (	
			SELECT 
				 Qtr
				,MAXQtd = MAX(DaysQTD)
			FROM tmpMSDN	
			GROUP BY 
				Qtr
		 ) q ON f.qtr = q.qtr
	GROUP BY 
	   f.[Qtr]
      ,f.[Mth]
      ,q.MAXQtd
)
	
SELECT f.[Qtr]
      ,f.[Mth]
      ,[Wk]
      ,[NoOfDays]
      ,[DaysMTD]
      ,c1.MaxMTD 
      ,[DaysQTD]
      ,c1.MaxQTD
  FROM [dbo].[tmpMSDN] f
  JOIN CTE1 c1 on f.qtr = c1.qtr and f.mth = c1.mth
  ORDER BY 1,2,3
GO		

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz