Post reply

Calculate growth - difference between currnet and previous row with a little twist

  • June 16, 2010 at 3:26 pm

    #220074

    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).

    ID TEST_DATE SCORE ROWNUM GROWTH calculation

    1 1/1/2010 83 1 null

    1 2/1/2010 51 2 12 (63-51)

    1 3/1/2010 63 3 -14 (49-63)

    1 4/1/2010 49 4 36 (85-49)

    1 5/1/2010 85 5 2 (85-83)

    2 1/1/2010 66 6 null

    2 2/1/2010 54 7 26 (80-54)

    2 3/1/2010 80 8 -4 (76-80)

    2 4/1/2010 76 9 10 (86-76)

    2 5/1/2010 86 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

    #1181315

    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,

    CASE WHEN 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

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • June 16, 2010 at 9:08 pm

    #1181340

    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