June 16, 2010 at 3:26 pm
Hi to all SQL lovers,
I am trying to do one tricky calculation. So far I've got half success. Here is a sample data ,desired column (in red) and calculation for the output column (in green).
IDTEST_DATESCOREROWNUMGROWTHcalculation
11/1/201083 1 null
12/1/201051 2 12(63-51)
13/1/201063 3 -14(49-63)
14/1/201049 4 36(85-49)
15/1/201085 5 2(85-83)
21/1/201066 6 null
22/1/201054 7 26(80-54)
23/1/201080 8 -4(76-80)
24/1/201076 9 10(86-76)
25/1/201086 10 20(86-66)
So what exactly I want to do is: for each ID, skip the first row and do the 3rd score - 2nd row score = 2nd row growth , 4rd score - 3nd row score = 3nd row growth and last row growth = last row score - first row score.
--sample data
IF OBJECT_ID('tempdb..#SAMPLE') IS NOT NULL
DROP TABLE #SAMPLE
CREATE TABLE #SAMPLE
(
ID INT,
TEST_DATE DATETIME,
SCORE INT,
ROWNUM INT IDENTITY(1,1)
)
INSERT INTO #SAMPLE
SELECT 1,'1/1/2010',83 UNION ALL
SELECT 1,'2/1/2010',51 UNION ALL
SELECT 1,'3/1/2010',63 UNION ALL
SELECT 1,'4/1/2010',49 UNION ALL
SELECT 1,'5/1/2010',85 UNION ALL
SELECT 2,'1/1/2010',66 UNION ALL
SELECT 2,'2/1/2010',54 UNION ALL
SELECT 2,'3/1/2010',80 UNION ALL
SELECT 2,'4/1/2010',76 UNION ALL
SELECT 2,'5/1/2010',86
--SELECT * FROM #SAMPLE
--my attempt with half success
SELECT Cur.ID,
--Prev.ID,
Prev.TEST_DATE,
Prev.SCORE,
GROWTH = Cur.SCORE - Prev.SCORE
FROM #SAMPLE Cur
LEFT OUTER JOIN #SAMPLE Prev
ON Cur.ROWNUM = Prev.ROWNUM + 1
WHERE Cur.TEST_DATE != '1/1/2010'
AND Prev.TEST_DATE != '1/1/2010'
my current output: (need to get first and last row for each id)
ID TEST_DATE SCORE GROWTH
1 2/1/2010 51 12
1 3/1/2010 63 -14
1 4/1/2010 49 36
2 2/1/2010 54 26
2 3/1/2010 80 -4
2 4/1/2010 76 10
Please guide me further to get all the rows. (I have both 2005 and 2008 R2, so solution in any version is fine with me)
June 16, 2010 at 5:18 pm
I'm quite sure this could be cleaned up a lot, but here's one quick solution. This should work on 2005+. As a side note, thanks so much for posting usable sample data, I almost passed this when I saw the normal copy/pasted data blob at the top but then realized that was just a result set. It's really nice when people do that, makes it a lot easier on us.
;WITH C1 AS (
SELECT S.*,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Test_Date DESC) RN,
COUNT(*) OVER (PARTITION BY ID) MRN
FROM #Sample S
)
SELECT A.ID, A.Test_Date, A.Score,
CASEWHEN A.RN = 1 THEN C.SCORE - D.Score
WHEN A.RN = A.MRN THEN NULL
ELSE B.Score - A.Score
END Growth
FROM C1 A
LEFT JOIN C1 B ON A.ID = B.ID AND A.RN = B.RN+1
LEFT JOIN C1 C ON A.ID = C.ID AND C.RN = 1
LEFT JOIN C1 D ON A.ID = D.ID AND D.RN = D.MRN
ORDER BY A.ID, A.RN DESC
June 16, 2010 at 9:08 pm
Thanks for the quick reply Garadin. I used your code logic in my real query and it worked great. :Wow: I didn't check query performance and all yet but it gave me a real good starting point.
and I was here to get help so its my duty to make things easier for the people who wants to help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply