August 31, 2011 at 3:22 am
Dear all,
With a table like this shown below, I'd like to use the Yearmonth value and then sum the last 3 months (from that yearmonth backwards) of Metric1 values, to create a new value Metric2 for that row.
Yearmonth, Metric1, Metric2
2010/01/01, 1, 0
2010/02/01, 2, 0
2010/03/01, 3, 0
2010/04/01, 4, 0
So the SQL code would change the table to be:
Yearmonth, Metric1, Metric2
2010/01/01, 1, 1
2010/02/01, 2, 3
2010/03/01, 3, 6
2010/04/01, 4, 9
There is only 1 row per month (these are always set to the first day of the month).
I wonder if anyone has a nice way of doing this ?
Regards, Greg
August 31, 2011 at 3:44 am
How's this?
--First, lets build some test data
DECLARE @TABLE AS TABLE (Yearmonth DATETIME, Metric1 INT, Metric2 INT)
INSERT INTO @TABLE (Yearmonth, Metric1)
SELECT '2010/01/01', 1 UNION ALL
SELECT '2010/02/01', 2 UNION ALL
SELECT '2010/03/01', 3 UNION ALL
SELECT '2010/04/01', 4
--Now lets get to work :)
UPDATE t1
SET Metric2 = ISNULL(RunningTotal,0)
FROM @TABLE t1
LEFT OUTER JOIN (SELECT Yearmonth, SUM(Metric1) AS RunningTotal
FROM (SELECT A.Yearmonth AS Yearmonth, B.Yearmonth AS Yearmonth_B,
B.Metric1
FROM (SELECT Yearmonth, Metric1, ROW_NUMBER() OVER(ORDER BY YearMonth) AS ID
FROM @TABLE) A
CROSS JOIN (SELECT Yearmonth, Metric1, ROW_NUMBER() OVER(ORDER BY YearMonth) AS ID
FROM @TABLE) B
WHERE (B.ID >= A.ID-2 AND B.ID <= A.ID) ) T
GROUP BY Yearmonth) tot ON t1.Yearmonth=tot.Yearmonth
WHERE tot.Yearmonth = t1.Yearmonth
SELECT * FROM @TABLE
--edit-- Sorry, wrote it as a SELECT statement. Changed to an UPDATE statement now.
August 31, 2011 at 3:47 am
What would be the value for month 5? Is the value 9 correct for month 4?
DROP TABLE #Sample
CREATE TABLE #Sample (Yearmonth DATE, Metric1 INT, Metric2 INT)
INSERT INTO #Sample (Yearmonth, Metric1, Metric2)
SELECT '2010/01/01', 1, NULL UNION ALL
SELECT '2010/02/01', 2, NULL UNION ALL
SELECT '2010/03/01', 3, NULL UNION ALL
SELECT '2010/04/01', 4, NULL UNION ALL
SELECT '2010/05/01', 5, NULL
SELECT * FROM #Sample
For 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
August 31, 2011 at 3:51 am
ChrisM@Work (8/31/2011)
What would be the value for month 5? Is the value 9 correct for month 4?
DROP TABLE #SampleCREATE TABLE #Sample (Yearmonth DATE, Metric1 INT, Metric2 INT)
INSERT INTO #Sample (Yearmonth, Metric1, Metric2)
SELECT '2010/01/01', 1, NULL UNION ALL
SELECT '2010/02/01', 2, NULL UNION ALL
SELECT '2010/03/01', 3, NULL UNION ALL
SELECT '2010/04/01', 4, NULL UNION ALL
SELECT '2010/05/01', 5, NULL
SELECT * FROM #Sample
Assuming I've understood the OP correctly, month 5 would be "12": -
Month 5 + Month 4 + Month 3 = 12.
August 31, 2011 at 4:08 am
Confirming this:
Month 4 would have metric2 as 9 - cos it would be 4+3+2.
Month 5 would have metric2 as 12 (5+4+3).
The problem would be that (and this is my fault for not explaining the test data well enough) there could be lots of Yearmonth rows and with some missing, so UNIONING them all may not be the best solution.
Regards, Greg
August 31, 2011 at 4:10 am
greg.bull (8/31/2011)
Confirming this:Month 4 would have metric2 as 9 - cos it would be 4+3+2.
Month 5 would have metric2 as 12 (5+4+3).
The problem would be that (and this is my fault for not explaining the test data well enough) there could be lots of Yearmonth rows and with some missing, so UNIONING them all may not be the best solution.
Regards, Greg
Did you check my solution? It works as you described. The "UNIONING" is to create test data since you didn't provide any, the actual code is below and written as an UPDATE statement.
--EDIT--
When you say some missing, what do you mean? Do you intend to SUM the last three known results, or are you only wanting to SUM the last 3 continuous rows?
e.g.
If we take the following data: -
DECLARE @TABLE AS TABLE (Yearmonth DATETIME, Metric1 INT, Metric2 INT)
INSERT INTO @TABLE (Yearmonth, Metric1)
SELECT '2010/01/01', 1 UNION ALL
SELECT '2010/02/01', 2 UNION ALL
SELECT '2010/03/01', 3 UNION ALL
SELECT '2010/04/01', 4 UNION ALL
SELECT '2010/06/01', 5
What would month "2010/06/01" be? Would you want it to be 12? Or would you want it to be 9 (as there is no "2010/05/01", the last three continuous results would be "2010/06/01" and "2010/04/01") ?
August 31, 2011 at 4:29 am
UPDATE #Sample SET Metric2 = ISNULL(NULLIF(Metric1+Metric1-1+Metric1-2,0),1)
😉
For 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
August 31, 2011 at 4:31 am
Right,
I was a bit confused re: the test data. I guess it's an easy way to insert it, as I didn't provide the insert values.
On to the next point, if a month row is missing, then it'd be ignored, so for month 6, we'd be summing month 6, month 5 (which'd be null) and month 4. We wouldn't want to find another month going back a bit more with non null data.
So I will go and try your sample code - thanks for your help.
G
August 31, 2011 at 4:35 am
Ah, thanks. The numbers don't run sequentially, I should have randomised my test data to make it more realistic. Sorry.
Regards, Greg
August 31, 2011 at 4:48 am
greg.bull (8/31/2011)
On to the next point, if a month row is missing, then it'd be ignored, so for month 6, we'd be summing month 6, month 5 (which'd be null) and month 4. We wouldn't want to find another month going back a bit more with non null data.
Ah, well this needs cleaning up but it gets the result you're after. I'm sure someone will make a much more efficient version 🙂
--First, lets build some test data
DECLARE @TABLE AS TABLE (Yearmonth DATETIME, Metric1 INT, Metric2 INT)
INSERT INTO @TABLE (Yearmonth, Metric1)
SELECT '2010/01/01', 1 UNION ALL
SELECT '2010/02/01', 2 UNION ALL
SELECT '2010/03/01', 3 UNION ALL
SELECT '2010/04/01', 4 UNION ALL
SELECT '2010/06/01', 5
--Tally table would be better, for testing purposes I've included one here "on the fly"
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS thedate
FROM t4 x, t4 y)
--Actual code -> It's a god-awful mess, and could do with some optimising but gets the correct result
UPDATE t2
SET Metric2 = ISNULL(RunningTotal,0)
FROM @TABLE t2
LEFT OUTER JOIN (SELECT a.Yearmonth, ISNULL(b.Metric1,0) AS Metric1
FROM (SELECT thedate AS Yearmonth
FROM (SELECT MIN(Yearmonth) minYearMonth, MAX(Yearmonth) maxYearMonth
FROM @TABLE) a
CROSS APPLY tally
WHERE minYearMonth <= thedate AND maxYearMonth >= thedate) a
LEFT OUTER JOIN @TABLE b ON a.Yearmonth = b.Yearmonth) t1 ON t1.Yearmonth = t2.Yearmonth
LEFT OUTER JOIN (SELECT Yearmonth, SUM(Metric1) AS RunningTotal
FROM (SELECT A.Yearmonth AS Yearmonth, B.Yearmonth AS Yearmonth_B,
B.Metric1
FROM (SELECT Yearmonth, Metric1, ROW_NUMBER() OVER(ORDER BY YearMonth) AS ID
FROM (SELECT a.Yearmonth, ISNULL(b.Metric1,0) AS Metric1
FROM (SELECT thedate AS Yearmonth
FROM (SELECT MIN(Yearmonth) minYearMonth, MAX(Yearmonth) maxYearMonth
FROM @TABLE) a
CROSS APPLY tally
WHERE minYearMonth <= thedate AND maxYearMonth >= thedate) a
LEFT OUTER JOIN @TABLE b ON a.Yearmonth = b.Yearmonth) a) A
CROSS JOIN (SELECT Yearmonth, Metric1, ROW_NUMBER() OVER(ORDER BY YearMonth) AS ID
FROM (SELECT a.Yearmonth, ISNULL(b.Metric1,0) AS Metric1
FROM (SELECT thedate AS Yearmonth
FROM (SELECT MIN(Yearmonth) minYearMonth, MAX(Yearmonth) maxYearMonth
FROM @TABLE) a
CROSS APPLY tally
WHERE minYearMonth <= thedate AND maxYearMonth >= thedate) a
LEFT OUTER JOIN @TABLE b ON a.Yearmonth = b.Yearmonth) a) B
WHERE (B.ID >= A.ID-2 AND B.ID <= A.ID) ) T
GROUP BY Yearmonth) tot ON t1.Yearmonth=tot.Yearmonth
WHERE t1.Yearmonth = t2.Yearmonth
SELECT * FROM @TABLE
--EDIT--
ChrisM@Work (8/31/2011)
UPDATE #Sample SET Metric2 = ISNULL(NULLIF(Metric1+Metric1-1+Metric1-2,0),1)😉
LOL!! Didn't think of that for the previous requirements. Awesome, will have to remember not to over-complicate things. . . *glances at new version of code*. . . hmmm :hehe:
August 31, 2011 at 5:22 am
Right, thanks for this, have submitted the code to 'extreme' stress testing and it's working well. I'll now integrate it and let you know. Thanks very much for you help, old chap.
August 31, 2011 at 6:52 am
If I wanted to sum up more than 1 metric in this way, how could this be done.
I've tried to amend the code, but I'm not sure I understand it all and so it now contains syntax errors that I can't address. I wonder if the tally table needs extending ? Here's what I had so far though, wonder if you can tweak ?
Regards, G
--First, lets build some test data
DECLARE @TABLE AS TABLE (Yearmonth DATETIME, Metric1 INT, Metric2 INT, Metric3 INT, Metric4 INT)
INSERT INTO @TABLE (Yearmonth, Metric1, Metric3)
SELECT '2010/02/01', 10, 100 UNION ALL
SELECT '2010/06/01', 2, 200 UNION ALL
SELECT '2010/03/01', 20, 2000 UNION ALL
SELECT '2010/04/01', 4, 40 UNION ALL
SELECT '2010/01/01', 30, 300
--Tally table would be better, for testing purposes I've included one here "on the fly"
;
WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS thedate
FROM t4 x, t4 y)
--Actual code -> It's a god-awful mess, and could do with some optimising but gets the correct result
UPDATE t2
SET Metric2 = ISNULL(RunningTotal,0), Metric4 = ISNULL(RunningTotal3,0)
FROM @TABLE t2
LEFT OUTER JOIN (SELECT a.Yearmonth, ISNULL(b.Metric1,0) AS Metric1, ISNULL(b.Metric3,0) as Metric3
/**/
FROM (SELECT thedate AS Yearmonth
FROM (SELECT MIN(Yearmonth) minYearMonth, MAX(Yearmonth) maxYearMonth
FROM @TABLE) a
CROSS APPLY tally
WHERE minYearMonth <= thedate AND maxYearMonth >= thedate) a
LEFT OUTER JOIN @TABLE b ON a.Yearmonth = b.Yearmonth) t1 ON t1.Yearmonth = t2.Yearmonth
/**/
LEFT OUTER JOIN (SELECT Yearmonth, SUM(Metric1) AS RunningTotal, SUM(Metric3) AS RunningTotal3
FROM (SELECT A.Yearmonth AS Yearmonth, B.Yearmonth AS Yearmonth_B,
B.Metric1, B.Metric3
FROM (SELECT Yearmonth, Metric1, Metric3, ROW_NUMBER() OVER(ORDER BY YearMonth) AS ID
/**/
FROM (SELECT a.Yearmonth, ISNULL(b.Metric1,0) AS Metric1, ISNULL(b.Metric3,0) as Metric3
FROM (SELECT thedate AS Yearmonth
FROM (SELECT MIN(Yearmonth) minYearMonth, MAX(Yearmonth) maxYearMonth
FROM @TABLE) a
CROSS APPLY tally
WHERE minYearMonth <= thedate AND maxYearMonth >= thedate) a
LEFT OUTER JOIN @TABLE b ON a.Yearmonth = b.Yearmonth) a) A
CROSS JOIN (SELECT Yearmonth, Metric1, Metric3 ROW_NUMBER() OVER(ORDER BY YearMonth) AS ID
FROM (SELECT a.Yearmonth, ISNULL(b.Metric1,0) AS Metric1 , ISNULL(b.Metric3,0) as Metric3
FROM (SELECT thedate AS Yearmonth
FROM (SELECT MIN(Yearmonth) minYearMonth, MAX(Yearmonth) maxYearMonth
FROM @TABLE) a
CROSS APPLY tally
WHERE minYearMonth <= thedate AND maxYearMonth >= thedate) a
LEFT OUTER JOIN @TABLE b ON a.Yearmonth = b.Yearmonth) a) B
WHERE (B.ID >= A.ID-2 AND B.ID <= A.ID) ) T
GROUP BY Yearmonth) tot ON t1.Yearmonth=tot.Yearmonth
WHERE t1.Yearmonth = t2.Yearmonth
SELECT * FROM @TABLE
August 31, 2011 at 7:15 am
The tally table is fine, it gives dates between 1900-01-01 and 7361-04-01 so you're unlikely to have hit the ceiling there 😛
Try this: -
--First, lets build some test data
DECLARE @TABLE AS TABLE (Yearmonth DATETIME, Metric1 INT, Metric2 INT, Metric3 INT, Metric4 INT)
INSERT INTO @TABLE (Yearmonth, Metric1, Metric3)
SELECT '2010/02/01', 10, 100 UNION ALL
SELECT '2010/06/01', 2, 200 UNION ALL
SELECT '2010/03/01', 20, 2000 UNION ALL
SELECT '2010/04/01', 4, 40 UNION ALL
SELECT '2010/01/01', 30, 300
--Tally table would be better, for testing purposes I've included one here "on the fly"
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS thedate
FROM t4 x, t4 y)
--Actual code -> It's a god-awful mess, and could do with some optimising but gets the correct result
UPDATE t2
SET Metric2 = ISNULL(RunningTotal,0), Metric4 = ISNULL(RunningTotal2,0)
FROM @TABLE t2
LEFT OUTER JOIN (SELECT a.Yearmonth, ISNULL(b.Metric1,0) AS Metric1, ISNULL(b.Metric3,0) AS Metric3
FROM (SELECT thedate AS Yearmonth
FROM (SELECT MIN(Yearmonth) AS minYearMonth, MAX(Yearmonth) AS maxYearMonth
FROM @TABLE) a
CROSS APPLY tally
WHERE minYearMonth <= thedate AND maxYearMonth >= thedate) a
LEFT OUTER JOIN @TABLE b ON a.Yearmonth = b.Yearmonth) t1 ON t1.Yearmonth = t2.Yearmonth
LEFT OUTER JOIN (SELECT Yearmonth, SUM(Metric1) AS RunningTotal, SUM(Metric3) AS RunningTotal2
FROM (SELECT A.Yearmonth AS Yearmonth, B.Metric1, B.Metric3
FROM (SELECT Yearmonth, Metric1, ROW_NUMBER() OVER(ORDER BY YearMonth) AS ID
FROM (SELECT a.Yearmonth, ISNULL(b.Metric1,0) AS Metric1
FROM (SELECT thedate AS Yearmonth
FROM (SELECT MIN(Yearmonth) minYearMonth, MAX(Yearmonth) maxYearMonth
FROM @TABLE) a
CROSS APPLY tally
WHERE minYearMonth <= thedate AND maxYearMonth >= thedate) a
LEFT OUTER JOIN @TABLE b ON a.Yearmonth = b.Yearmonth) a) A
CROSS JOIN (SELECT Yearmonth, Metric1, ROW_NUMBER() OVER(ORDER BY YearMonth) AS ID, Metric3
FROM (SELECT a.Yearmonth, ISNULL(b.Metric1,0) AS Metric1, ISNULL(b.Metric3,0) AS Metric3
FROM (SELECT thedate AS Yearmonth
FROM (SELECT MIN(Yearmonth) minYearMonth, MAX(Yearmonth) maxYearMonth
FROM @TABLE) a
CROSS APPLY tally
WHERE minYearMonth <= thedate AND maxYearMonth >= thedate) a
LEFT OUTER JOIN @TABLE b ON a.Yearmonth = b.Yearmonth) a) B
WHERE (B.ID >= A.ID-2 AND B.ID <= A.ID) ) T
GROUP BY Yearmonth) tot ON t1.Yearmonth=tot.Yearmonth
WHERE t1.Yearmonth = t2.Yearmonth
SELECT * FROM @TABLE
ORDER BY Yearmonth
August 31, 2011 at 8:42 am
OK, I see what's happening here a little more clearly, at least with the tally table.
However, would you believe it (;-)) I am still a bit stuck, as I see there are in fact other non metric values in the mix that I need to group on. This means there'd be 1 row for each unique combination of location, grade and yearmonth.
I made up some test data to illustrate. But I can't work out where to add the extra grouping additional and select columns - it's just too complex for me to really understand. Umm, I wonder if you are able to help ?
DECLARE @TABLE AS TABLE (Location VARCHAR(50), Grade VARCHAR(50), Yearmonth DATETIME, Metric1 INT, Metric2 INT, Metric3 INT, Metric4 INT)
INSERT INTO @TABLE (Location, Grade, Yearmonth, Metric1, Metric3)
SELECT 'Small House', 'A', 2010/02/01', 10, 100 UNION ALL
SELECT 'Big House', 'B','2010/06/01', 2, 200 UNION ALL
SELECT 'Big House', 'A','2010/06/01', 20, 2000 UNION ALL
SELECT 'Small House', 'A','2010/04/01', 4, 40 UNION ALL
SELECT 'Small House', 'A','2010/01/01', 30, 300
Regards, Greg.
August 31, 2011 at 8:46 am
Could you show me the expected output?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply