Missing months group by
- Other than making the batch dynamic, this should be very close:
DROP TABLE #SummedData
-- create a matrix of date, country and type and left-join the data set to it
-- so as to backfill "missing" results. Calculate running totals - it's convenient here.
;WITH Matrix AS (
SELECT [Bdate] = DATEADD(MONTH,n,StartMonth), y.Country, z.[Type]
FROM (SELECT StartMonth = MIN(Bdate), EndMonth = MAX(Bdate) FROM #temp4) m
CROSS APPLY (
SELECT TOP(1+DATEDIFF(MONTH,m.StartMonth,m.EndMonth)) n
FROM (VALUES (0),(1),(2),(3),(4),(5)) d (n)
) x
CROSS JOIN (SELECT DISTINCT Country FROM #temp4) y
CROSS JOIN (VALUES ('A'), ('B')) z ([Type])
)
SELECT m.Bdate, m.Country, m.[Type],
P1 = SUM(ISNULL(t.P1,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
P2 = SUM(ISNULL(t.P2,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
C1 = SUM(ISNULL(t.C1,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
C2 = SUM(ISNULL(t.C2,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
R1 = SUM(ISNULL(t.R1,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate)
INTO #SummedData
FROM Matrix m
LEFT JOIN #temp4 t
ON t.Bdate = m.Bdate
AND t.Country = m.Country
AND t.[Type] = m.[Type]
-- swivel the backfilled result
SELECT x.[Desc], t.[Type], t.Country,
'MAR-17' = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
'APR-17' = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
'MAY-17' = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
'JUN-17' = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
'JUL-17' = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #SummedData t
CROSS APPLY (VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = 'A' AND x.[Desc] IN ('P1','P2'))
OR (t.[Type] = 'B' AND x.[Desc] IN ('C1','C2','R1'))
ORDER BY t.Country, t.[Type], x.ReportOrder“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - ChrisM@Work - Monday, June 19, 2017 10:12 AM
Yes it is displaying correct result now even if there is no data. Can you please help with dynamic dates,right now it shows data for first 6 dates only .
-
WITH calendarMonths(FirstOfMonth)
AS
(SELECT DateAdd(mm, number, '20170101') FROM master.dbo.spt_values sv WHERE type='P')
SELECT
calendarMonths.FirstOfMonth
FROM calendarMonths;
- Joe Torre - Monday, June 19, 2017 2:25 PM
WITH calendarMonths(FirstOfMonth)
AS
(SELECT DateAdd(mm, number, '20170101') FROM master.dbo.spt_values sv WHERE type='P')
SELECT
calendarMonths.FirstOfMonth
FROM calendarMonths;How to add it to my main code to show data between min and max dates?
- The next step is to construct the statement as a string so it can be executed.
Here's the entire output list constructed as a string from the source table:-- list of dates
DECLARE @OutputList VARCHAR(5000) = 'SELECT x.[Desc], t.[Type], t.Country';WITH ListOfDates AS (
SELECT
[Title] = STUFF(RIGHT(CONVERT(CHAR(9),DATEADD(MONTH,n,StartMonth),6),6),4,1,'-'),
[Bdate] = DATEADD(MONTH,n,StartMonth)
FROM (SELECT StartMonth = MIN(Bdate), EndMonth = MAX(Bdate) FROM #temp4) m
CROSS APPLY (
SELECT TOP(1+DATEDIFF(MONTH,m.StartMonth,m.EndMonth)) n
FROM (VALUES (0),(1),(2),(3),(4),(5)) d (n)
) x
)
SELECT @OutputList = @OutputList + ',' + CHAR(13) + '['+[Title]+'] = SUM(CASE WHEN t.Bdate = ''' + CONVERT(VARCHAR,[Bdate],112) + ''' THEN x.[Value] ELSE 0 END)'
FROM ListOfDatesPRINT @OutputListAll you have to do is reconstruct the whole query using this as a starting point. Personally, I'd put the next bit (the body of the query) into a second variable and then concatenate them to make the whole query. Have a try, holler if you get stuck.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - ChrisM@Work - Tuesday, June 20, 2017 4:39 AM
I am not sure how do i reconstruct it. can you help.
-
You (Papil), need to understand how the query works. I'm not sure you do, by your questions. Ask questions about the query, not the result.
- Steve Jones - SSC Editor - Wednesday, June 21, 2017 7:59 AMPapil, I have to agree with Steve. I've been hoping that you would have gone to some effort made some attempt to continue the project - we're on different timezones, after all. I'm disappointed that you have not. Here's all you need to complete the query. See what you can do. Run it and look at the [messages] tab in SSMS.
if 0 = 1 begin
CREATE TABLE #temp4(
Bdate DATE
,Country VARCHAR(7)
,Type VARCHAR(4)
,P1 INT
,P2 INT
,C1 INT
,C2 INT
,R1 INT
);INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','IN','A','20','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','B','0','0','10','10','5');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','A','10','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','US','A','2','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','US','A','2','20','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('7/1/2017','US','A','2','8','0','0','0');
end
-------------------------------------------------------------
IF OBJECT_ID('tempdb..#SummedData') IS NOT NULL DROP TABLE #SummedData;
-- create a matrix of date, country and type and left-join the data set to it
-- so as to backfill "missing" results. Calculate running totals - it's convenient here.
;WITH Matrix AS (
SELECT [Bdate] = DATEADD(MONTH,n,StartMonth), y.Country, z.[Type]
FROM (SELECT StartMonth = MIN(Bdate), EndMonth = MAX(Bdate) FROM #temp4) m
CROSS APPLY (
SELECT TOP(1+DATEDIFF(MONTH,m.StartMonth,m.EndMonth)) n
FROM (VALUES (0),(1),(2),(3),(4),(5)) d (n)
) x
CROSS JOIN (SELECT DISTINCT Country FROM #temp4) y
CROSS JOIN (VALUES ('A'), ('B')) z ([Type])
)
SELECT m.Bdate, m.Country, m.[Type],
P1 = SUM(ISNULL(t.P1,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
P2 = SUM(ISNULL(t.P2,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
C1 = SUM(ISNULL(t.C1,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
C2 = SUM(ISNULL(t.C2,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
R1 = SUM(ISNULL(t.R1,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate)
INTO #SummedData
FROM Matrix m
LEFT JOIN #temp4 t
ON t.Bdate = m.Bdate
AND t.Country = m.Country
AND t.[Type] = m.[Type]
-- Swivel the backfilled result. This hardcoded query must be constructed as dynamic SQL
-- in order to manage the dates.
SELECT x.[Desc], t.[Type], t.Country,
[MAR-17] = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
[APR-17] = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
[MAY-17] = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
[JUN-17] = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
[JUL-17] = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #SummedData t
CROSS APPLY (VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = 'A' AND x.[Desc] IN ('P1','P2'))
OR (t.[Type] = 'B' AND x.[Desc] IN ('C1','C2','R1'))
ORDER BY t.Country, t.[Type], x.ReportOrder
-- List of dates, and SELECT list of query
DECLARE @OutputList VARCHAR(8000) = 'SELECT x.[Desc], t.[Type], t.Country';WITH ListOfDates AS (
SELECT
[Title] = STUFF(RIGHT(CONVERT(CHAR(9),DATEADD(MONTH,n,StartMonth),6),6),4,1,'-'),
[Bdate] = DATEADD(MONTH,n,StartMonth)
FROM (SELECT StartMonth = MIN(Bdate), EndMonth = MAX(Bdate) FROM #temp4) m
CROSS APPLY (
SELECT TOP(1+DATEDIFF(MONTH,m.StartMonth,m.EndMonth)) n
FROM (VALUES (0),(1),(2),(3),(4),(5)) d (n)
) x
)
SELECT @OutputList = @OutputList + ',' + CHAR(13) + ' ['+[Title]+'] = SUM(CASE WHEN t.Bdate = ''' + CONVERT(VARCHAR,[Bdate],112) + ''' THEN x.[Value] ELSE 0 END)'
FROM ListOfDatesPRINT @OutputList
-- Construct the FROM list of the query
DECLARE @FROMlist VARCHAR(8000) = '
FROM #SummedData t
CROSS APPLY (VALUES ([P1],P1,1),([P2],P2,2),([C1],C1,3),([C2],C2,4),([R1],R1,5)) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = ''A'' AND x.[Desc] IN (''P1'',''P2''))
OR (t.[Type] = ''B'' AND x.[Desc] IN (''C1'',''C2'',''R1''))
ORDER BY t.Country, t.[Type], x.ReportOrder'PRINT @FROMlist“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - ChrisM@Work - Wednesday, June 21, 2017 8:33 AM
Chris,Can you pls explain the string conversion part to once -the 2 variables that you have created so that i can try it on my own further. thanks .
-
if 0 = 1 begin
CREATE TABLE #temp4(
Bdate DATE
,Country VARCHAR(7)
,Type VARCHAR(4)
,P1 INT
,P2 INT
,C1 INT
,C2 INT
,R1 INT
);INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','IN','A','20','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','B','0','0','10','10','5');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','A','10','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','US','A','2','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','US','A','2','20','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('7/1/2017','US','A','2','8','0','0','0');
end-------------------------------------------------------------
IF OBJECT_ID('tempdb..#SummedData') IS NOT NULL DROP TABLE #SummedData;
-- create a matrix of date, country and type and left-join the data set to it
-- so as to backfill "missing" results. Calculate running totals - it's convenient here.
;WITH Matrix AS (
SELECT [Bdate] = DATEADD(MONTH,n,StartMonth), y.Country, z.[Type]
FROM (SELECT StartMonth = MIN(Bdate), EndMonth = MAX(Bdate) FROM #temp4) m
CROSS APPLY (
SELECT TOP(1+DATEDIFF(MONTH,m.StartMonth,m.EndMonth)) n
FROM (VALUES (0),(1),(2),(3),(4),(5)) d (n)
) x
CROSS JOIN (SELECT DISTINCT Country FROM #temp4) y
CROSS JOIN (VALUES ('A'), ('B')) z ([Type])
)
SELECT m.Bdate, m.Country, m.[Type],
P1 = SUM(ISNULL(t.P1,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
P2 = SUM(ISNULL(t.P2,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
C1 = SUM(ISNULL(t.C1,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
C2 = SUM(ISNULL(t.C2,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate),
R1 = SUM(ISNULL(t.R1,0)) OVER(PARTITION BY m.Country, m.[Type] ORDER BY m.Bdate)
INTO #SummedData
FROM Matrix m
LEFT JOIN #temp4 t
ON t.Bdate = m.Bdate
AND t.Country = m.Country
AND t.[Type] = m.[Type]
-- Swivel the backfilled result. This is the hardcoded version of the query,
-- which must be constructed as dynamic SQL in order to manage the dates.
SELECT x.[Desc], t.[Type], t.Country,
[MAR-17] = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
[APR-17] = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
[MAY-17] = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
[JUN-17] = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
[JUL-17] = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #SummedData t
CROSS APPLY (VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = 'A' AND x.[Desc] IN ('P1','P2'))
OR (t.[Type] = 'B' AND x.[Desc] IN ('C1','C2','R1'))
ORDER BY t.Country, t.[Type], x.ReportOrder
-- List of dates, and SELECT list of query
DECLARE @OutputList VARCHAR(8000) = 'SELECT x.[Desc], t.[Type], t.Country';WITH ListOfDates AS (
SELECT
[Title] = STUFF(RIGHT(CONVERT(CHAR(9),DATEADD(MONTH,n,StartMonth),6),6),4,1,'-'),
[Bdate] = DATEADD(MONTH,n,StartMonth)
FROM (SELECT StartMonth = MIN(Bdate), EndMonth = MAX(Bdate) FROM #temp4) m
CROSS APPLY (
SELECT TOP(1+DATEDIFF(MONTH,m.StartMonth,m.EndMonth)) n
FROM (VALUES (0),(1),(2),(3),(4),(5)) d (n)
) x
)
SELECT @OutputList = @OutputList + ',' + CHAR(13) + ' ['+[Title]+'] = SUM(CASE WHEN t.Bdate = ''' + CONVERT(VARCHAR,[Bdate],112) + ''' THEN x.[Value] ELSE 0 END)'
FROM ListOfDates
-- Construct the FROM list of the query
DECLARE @FROMlist VARCHAR(8000) = '
FROM #SummedData t
CROSS APPLY (VALUES (''P1'',P1,1),(''P2'',P2,2),(''C1'',C1,3),(''C2'',C2,4),(''R1'',R1,5)) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = ''A'' AND x.[Desc] IN (''P1'',''P2''))
OR (t.[Type] = ''B'' AND x.[Desc] IN (''C1'',''C2'',''R1''))
ORDER BY t.Country, t.[Type], x.ReportOrder'
-- Compare the constructed string against the original query,
-- also provides you with a copy to run for error checking
PRINT @OutputList + @FROMlist
-- Execute the constructed query
EXEC (@OutputList + @FROMlist)“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply