August 31, 2011 at 8:58 am
Yes, thanks. For this test data
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
I'd like to see the new metrics (derived from those running totals) setting purposely 0 metrics in @TABLE. So we'd use the below for an update script joining on a.location = b.location, a.grade = b.grade and a.yearmonth = b.yearmonth
Location, Grade, Yearmonth, Aggregate1, Aggregate3
'Small House', 'A', 2010/02/01', 40, 400
'Big House', 'B','2010/06/01', 2, 200
'Big House', 'A','2010/06/01', 20, 2000
'Small House', 'A','2010/04/01', 14, 50
'Small House', 'A','2010/01/01', 30, 300
I think that is it !
Regards, Greg.
August 31, 2011 at 9:26 am
Hmmm, not sure that I understand correctly.
(Sorry if I'm a bit slow replying, got a lovely issue that I'm solving at work)
Currently, the code I've supplied would show the following if executed against your new test data: -
Location Grade Yearmonth Metric1 Metric2 Metric3 Metric4
-------------------------------------------------- -------------------------------------------------- ----------------------- ----------- ----------- ----------- -----------
Small House A 2010-01-01 00:00:00.000 30 30 300 300
Small House A 2010-02-01 00:00:00.000 10 40 100 400
Small House A 2010-04-01 00:00:00.000 4 14 40 140
Big House B 2010-06-01 00:00:00.000 2 28 200 2440
Big House A 2010-06-01 00:00:00.000 20 28 2000 2440
August 31, 2011 at 9:29 am
Cadavre (8/31/2011)
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 dataDECLARE @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:
Simple is good...
DROP TABLE #Sample
CREATE TABLE #Sample (Yearmonth DATE, Metric1 INT, Metric2 INT NULL)
INSERT INTO #Sample (Yearmonth, Metric1, Metric2)
SELECT '2010/01/01', 1, NULL UNION ALL
SELECT '2010/03/01', 3, NULL UNION ALL
SELECT '2010/02/01', 2, NULL UNION ALL
SELECT '2010/04/01', 4, NULL UNION all
SELECT '2010/06/01', 5, NULL
SELECT s.Yearmonth, s.Metric1, iTVF1.Metric2
FROM #Sample s
CROSS APPLY(SELECT Metric2 = SUM(Metric1) FROM #Sample WHERE DATEDIFF(mm,Yearmonth,s.Yearmonth) BETWEEN 0 AND 2) iTVF1
ORDER BY Yearmonth
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 9:34 am
Nice one Chris 😉
Told you someone would simplify it Greg
August 31, 2011 at 9:41 am
Wont this do?
Shamelessly utilizing the sample data that Cadavre prepared( Thanks Cad)
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/05/01', 5
--== This is added to give the flexibility of changing the number of month you need the Metric2 for
-- As you initially requested for 3, i have used 3.
DECLARE @NumberOfMonthsToFindPreviousSum INT
SET @NumberOfMonthsToFindPreviousSum = 3
SET @NumberOfMonthsToFindPreviousSum = -1 * @NumberOfMonthsToFindPreviousSum
SELECT OuterTable.Yearmonth , OuterTable.Metric1 ,CrsAPP.Metric2
FROM @TABLE OuterTable
CROSS APPLY
( SELECT SUM( InnerTable.Metric1 ) Metric2
FROM @TABLE InnerTable
WHERE InnerTable.Yearmonth <= OuterTable.Yearmonth AND
InnerTable.Yearmonth > DATEADD(MONTH ,@NumberOfMonthsToFindPreviousSum, OuterTable.Yearmonth )
) CrsAPP
ORDER BY OuterTable.Metric1
August 31, 2011 at 9:43 am
OK thanks chaps, I'll just have a play and try to integrate. Really appreciate your time here.
Greg
August 31, 2011 at 9:44 am
Cadavre (8/31/2011)
Nice one Chris 😉Told you someone would simplify it Greg
Wooooosh... i dint see Chris posted a similar solution 😉
{ Note to self : Brush the teeth before even reading posts :hehe::hehe: }
August 31, 2011 at 9:44 am
Cadavre (8/31/2011)
Nice one Chris 😉Told you someone would simplify it Greg
Wonder if it works on the real data though?
My money's on CC's script, it's more flexible 😎
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 11:25 am
And with proper index on YearMonth column, the script should complete in a jiffy. I wonder if a Quirky Update code can be bestowed on this puzzle :unsure:
September 1, 2011 at 4:48 am
Chaps,
Just to let you know, all working properly when integrated into my real database, so thanks for your efforts and you can all feel good about yourselves now ! 🙂
Regards, Greg.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply