Sum Pivot by Dynamic Quarter
-
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.
-
This query is close. The only thing it isn't doing QUITE right is the ordering for the Quarters:
USE [Admin];
GO
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
);
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( (
SELECTDISTINCT
',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];
GO
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 @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 < 4
BEGIN
IF (LEN(@cols) = 0)
BEGIN
SET @cols = ISNULL( STUFF( (
SELECTDISTINCT
',' + 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((
SELECTDISTINCT
',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( (
SELECTDISTINCT
',' + 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((
SELECTDISTINCT
',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;
SELECTTOP (1)
@cols = @cols + N',' + ISNULL( QUOTENAME([ship])
, ''
)
FROM[#temp]
WHERE[ship] LIKE ('%Quarter' + CAST(@minQuarter AS VARCHAR(25)));
SELECTTOP (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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
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?
-
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply