# Sum Pivot by Dynamic Quarter

• girl_bj

SSCommitted

Points: 1724

i have a report to create a prediction for 12 months (yearly prediction by month). example when will be the shipment date is base on input from user. the report will run by month. for example this month is July, shipment will only show July onward. i manage to create the dynamic pivot. how can i include the by quarter in between? i am not sure how to count the quarter.

`create table #temp(    country varchar(20),    ship varchar(20),    amount money)insert into #temp values ('German', '2020-07', 1000.00)insert into #temp values ('USA', '2020-07', 500.00)insert into #temp values ('AUS', '2020-09', 800.00)insert into #temp values ('AUS', '2020-08', 700.00)insert into #temp values ('German', '2020-12', 1100.00)insert into #temp values ('UK', '2021-03', 1100.00)insert into #temp values ('Africa', '2021-02', 1100.00)DECLARE @cols AS NVARCHAR(MAX),    @query  AS NVARCHAR(MAX)    ,@cols2 AS NVARCHAR(MAX)SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(ship)             FROM #temp c            FOR XML PATH(''), TYPE            ).value('.', 'NVARCHAR(MAX)')         ,1,1,'')set @cols2 =STUFF(( select distinct ',IsNull(['+ship+'],0) as ['+ship+']' from #temp             FOR XML PATH(''), TYPE            ).value('.', 'NVARCHAR(MAX)')         ,1,1,'')set @query = 'SELECT country, ' + @cols2 + '  from             (                select country                    , amount                    , ship                from #temp           ) x            pivot             (                 max(amount)                for ship in (' + @cols + ')            ) p 'execute(@query)`

I would like to include in between by Quarter 1,2,3,4. Is this possible? Currently I am doing it manually in excel.

• Mr. Brian Gale

SSC-Insane

Points: 23053

This query is close.  The only thing it isn't doing QUITE right is the ordering for the Quarters:

`USE [Admin];GOCREATE TABLE [#temp](	[country]  VARCHAR(20)	, [ship]   VARCHAR(20)	, [amount] MONEY);INSERT INTO [#temp]VALUES	(		'German'		, '2020-07'		, 1000.00	);INSERT INTO [#temp]VALUES	(		'USA'		, '2020-07'		, 500.00	);INSERT INTO [#temp]VALUES	(		'AUS'		, '2020-09'		, 800.00	);INSERT INTO [#temp]VALUES	(		'AUS'		, '2020-08'		, 700.00	);INSERT INTO [#temp]VALUES	(		'German'		, '2020-12'		, 1100.00	);INSERT INTO [#temp]VALUES	(		'UK'		, '2021-03'		, 1100.00	);INSERT INTO [#temp]VALUES	(		'Africa'		, '2021-02'		, 1100.00	);INSERT INTO [#temp](	[country]	, [ship]	, [amount]) SELECT DISTINCT		[country]		, LEFT([ship], 5) + 'Quarter' + CAST(DATEPART(	 QUARTER														 , CAST([ship] + '-01' AS DATE)													 ) AS VARCHAR(255))		, SUM([amount]) OVER (PARTITION BY								  [country]								  , DATEPART(	QUARTER												, CAST([ship] + '-01' AS DATE)											)							 )  FROM	[#temp];DECLARE	@cols	 AS NVARCHAR(MAX)	, @query AS NVARCHAR(MAX)	, @cols2 AS NVARCHAR(MAX);SET @cols = STUFF(	 (						 SELECT DISTINCT								',' + QUOTENAME([c].[ship])						  FROM	[#temp] AS [c]						 FOR XML PATH(''), TYPE					 ).[value](	  '.'								  , 'NVARCHAR(MAX)'							  )					 , 1					 , 1					 , ''				 );SET @cols2 = STUFF(	  (						  SELECT	DISTINCT									',IsNull([' + [ship] + '],0) as [' + [ship] + ']'						   FROM		[#temp]						  FOR XML PATH(''), TYPE					  ).[value](   '.'								   , 'NVARCHAR(MAX)'							   )					  , 1					  , 1					  , ''				  );SET @query = N'SELECT country, ' + @cols2 + N'  from             (                select country                    , amount                    , ship                from #temp           ) x            pivot             (                 max(amount)                for ship in (' + @cols + N')            ) p ';EXECUTE (@query);DROP TABLE [#temp];`

Is that close enough or do you need the ordering of the quarters to be correct?

EDIT REASON - ran SQL Prompt SQL Format on the code to make it easier to read.

EDIT 2: Query which has the order you are looking for:

`USE [Admin];GOCREATE TABLE [#temp](	[country]  VARCHAR(20)	, [ship]   VARCHAR(20)	, [amount] MONEY);INSERT INTO [#temp]VALUES	(		'German'		, '2020-07'		, 1000.00	);INSERT INTO [#temp]VALUES	(		'USA'		, '2020-07'		, 500.00	);INSERT INTO [#temp]VALUES	(		'AUS'		, '2020-09'		, 800.00	);INSERT INTO [#temp]VALUES	(		'AUS'		, '2020-08'		, 700.00	);INSERT INTO [#temp]VALUES	(		'German'		, '2020-12'		, 1100.00	);INSERT INTO [#temp]VALUES	(		'UK'		, '2021-03'		, 1100.00	);INSERT INTO [#temp]VALUES	(		'Africa'		, '2021-02'		, 1100.00	);DECLARE @minQuarter SMALLINT;SELECT		@minQuarter = DATEPART(	  QUARTER								  , MIN(CAST([ship] + '-01' AS DATE))							  ) FROM	[#temp];INSERT INTO [#temp](	[country]	, [ship]	, [amount]) SELECT DISTINCT		[country]		, LEFT([ship], 5) + 'Quarter' + CAST(DATEPART(	 QUARTER														 , CAST([ship] + '-01' AS DATE)													 ) AS VARCHAR(255))		, SUM([amount]) OVER (PARTITION BY								  [country]								  , DATEPART(	QUARTER												, CAST([ship] + '-01' AS DATE)											)							 )  FROM	[#temp];DECLARE	@cols	 AS NVARCHAR(MAX) = N''	, @query AS NVARCHAR(MAX)	, @cols2 AS NVARCHAR(MAX) = N'';DECLARE @iteration INT = 0;WHILE @iteration < 4BEGIN	IF (LEN(@cols) = 0)	BEGIN		SET @cols = ISNULL(	  STUFF(   (										   SELECT	DISTINCT													',' + QUOTENAME([c].[ship])											FROM	[#temp] AS [c]											WHERE	[c].[ship] NOT LIKE '%Quarter%'													AND DATEPART(	QUARTER																	, CAST([c].[ship] + '-01' AS DATE)																) = @minQuarter										   FOR XML PATH(''), TYPE									   ).[value](	'.'													, 'NVARCHAR(MAX)'												)									   , 1									   , 1									   , ''								   )							  , ''						  );		SET @cols2 = ISNULL(   STUFF(	(											SELECT	DISTINCT													',IsNull([' + [ship] + '],0) as [' + [ship] + ']'											 FROM	[#temp]											 WHERE	[ship] NOT LIKE '%Quarter%'													AND DATEPART(	QUARTER																	, CAST([ship] + '-01' AS DATE)																) = @minQuarter											FOR XML PATH(''), TYPE										).[value](	 '.'													 , 'NVARCHAR(MAX)'												 )										, 1										, 1										, ''									)							   , ''						   );	END;	ELSE	BEGIN		SET @cols = @cols + N',' + ISNULL(	 STUFF(	  (														  SELECT	DISTINCT																	',' + QUOTENAME([c].[ship])														   FROM		[#temp] AS [c]														   WHERE	[c].[ship] NOT LIKE '%Quarter%'																	AND DATEPART(	QUARTER																					, CAST([c].[ship] + '-01' AS DATE)																				) = @minQuarter														  FOR XML PATH(''), TYPE													  ).[value](   '.'																   , 'NVARCHAR(MAX)'															   )													  , 1													  , 1													  , ''												  )											 , ''										 );		SET @cols2 = @cols2 + N',' + ISNULL(   STUFF(	(															SELECT	DISTINCT																	',IsNull([' + [ship] + '],0) as [' + [ship] + ']'															 FROM	[#temp]															 WHERE	[ship] NOT LIKE '%Quarter%'																	AND DATEPART(	QUARTER																					, CAST([ship] + '-01' AS DATE)																				) = @minQuarter															FOR XML PATH(''), TYPE														).[value](	 '.'																	 , 'NVARCHAR(MAX)'																 )														, 1														, 1														, ''													)											   , ''										   );	END;	SELECT	TOP (1)			@cols = @cols + N',' + ISNULL(	 QUOTENAME([ship])											 , ''										 )	 FROM	[#temp]	 WHERE	[ship] LIKE ('%Quarter' + CAST(@minQuarter AS VARCHAR(25)));	SELECT	TOP (1)			@cols2 = @cols2 + ISNULL(	',ISNULL([' + [ship] + '],0) AS [' + [ship] + ']'										, ''									)	 FROM	[#temp]	 WHERE	[ship] LIKE ('%Quarter' + CAST(@minQuarter AS VARCHAR(25)));	SET @iteration = @iteration + 1;	SET @minQuarter = @minQuarter + 1;	IF @minQuarter = 5	BEGIN		SET @minQuarter = 1;	END;	PRINT @iteration;	PRINT @cols;	PRINT @cols2;END;IF (RIGHT(@cols, 1) = ',')BEGIN	SET @cols = SUBSTRING(	 @cols							 , 0							 , LEN(@cols)						 );	SET @cols2 = SUBSTRING(	  @cols2							  , 0							  , LEN(@cols2)						  );END;SET @query = N'SELECT country, ' + @cols2 + N'  from             (                select country                    , amount                    , ship                from #temp           ) x            pivot             (                 max(amount)                for ship in (' + @cols + N')            ) p ';EXECUTE (@query);DROP TABLE [#temp];`

Now the only thing it doesn't do (but your expected output doesn't either) is show you quarters where the value is 0 (in this case quarter 2).  Plus it only handles 4 quarters properly.  If the data extends further than 4 quarters, then you will have problems.

• girl_bj

SSCommitted

Points: 1724

Hi, thanks Brian. Edit 1 is the one im looking for to sum by quarter. But the ordering are not in order. How can I order it without applying Order by with case condition?

• Mr. Brian Gale

SSC-Insane

Points: 23053

Can you provide some sample data where it is not ordered correctly?  All of my testing on my system indicates that it should  be ordered correctly EXCEPT if you go past 4 quarters.  The script relies HEAVILY on the time frame being 4 quarters or less.  If it exceeds 4 quarters, then it will fail.  If you need it to span more than 4 quarters, my approach gets much more messy and there are likely better approaches in that case.

Once you go past 4 quarters you get the problem of 2020-Quarter1 showing totals from 2021-Quarter1 as well.  Your sample data looked like it was only for a 1 year period and thus should be fine.

The other use case where you may hit snags is if you use single-digit months anywhere as you are storing it as a string.  So 2020-1 is going to be greater than 2020-02 and thus will show up AFTER.  ie the ordering will be 2020-02 then 2020-1.  And if you use a mix of "01" and "1" for the month, they will both show up.

If you need to span more than 4 quarters (or you have some sample data that breaks the ordering), let me know and I can try to figure out how to fix it.

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

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