Post reply

SQL to do calc on different rows

  • August 10, 2013 at 12:05 pm

    #281037

    Hi, all

    Is there any anylytical function in ss so I can achive result like belowo doing calculation on values from different rows?

    Thanks all for help, I'm sure it should be, I think there is one in oracle ,but I could not find in ss.

    Thanks all

    Mario

    select * into #t1 from (

    select 'alpha' c1, 100 c2 union

    select 'bravo' c1, 120 c2 union

    select 'charlie' c1, 230 c2 union

    select 'delta' c1, 360 c2 union

    select 'whiskey' c1, 500 c2 ) b

    --delta1 = row2.c2 - row1.c1

    --delta2 = row2.c3 - row1.c2 etc..

    c1 | c2 | delta |

    alpha | 100 | 20 |

    bravo | 120 | 110 |

    charlie | 230 | 130 |

    delta | 360 | 140 |

    whiskey | 500 | 0 |

  • August 10, 2013 at 7:34 pm

    #1640394

    Use ROW_NUMBER().

    This is close, but not quite right...

    WITH SomeNumbers AS

    (

    SELECT c1, c2,

    ROW_NUMBER() OVER (ORDER BY c1) AS RowNumber

    FROM #t1

    )

    SELECT x.c1

    , x.c2

    , x.PrevC2

    , x.c2-COALESCE(x.PrevC2,0) AS Delta

    FROM (SELECT s1.c1

    , s1.c2

    --, s1.RowNumber

    --, s2.c1 As PrevC1

    , s2.c2 As PrevC2

    FROM SomeNumbers s1

    LEFT JOIN SomeNumbers s2 ON (s1.RowNumber=s2.RowNumber+1)) x;

    alpha100NULL100

    bravo12010020

    charlie230120110

    delta360230130

    whiskey500360140

  • August 11, 2013 at 12:47 am

    #1640400

    Thanks, pietlinden !!!!

    I got and idea

    Best

    Mario

  • August 11, 2013 at 12:56 am

    #1640402

    This gives you the output that you specified:

    with cte as

    (

    Select c1, c2,

    Row_Number() over(ORDER BY c1) as RowNum

    from #t1

    )

    Select c.c1, c.c2, Coalesce(n.c2-c.c2, 0) as delta

    from cte as c

    left outer join cte as p

    on c.RowNum = p.RowNum + 1

    left outer join cte as n

    on c.RowNum = N.RowNum - 1

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • August 12, 2013 at 5:48 am

    #1640518

    SELECT

    tr.*,

    ISNULL(nr.c2 - tr.c2,0)

    FROM #t1 tr

    OUTER APPLY (

    SELECT TOP 1 *

    FROM #t1 ti

    WHERE ti.c1 > tr.c1

    ORDER BY ti.c1 ASC

    ) nr

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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 24, 2013 at 9:51 am

    #1644655

    Oh, super cool! Guess I gotta read (and re-read) Paul's article until I understand APPLY, then...

  • August 24, 2013 at 3:45 pm

    #1644673

    To answer the original question, yes there is:

    SELECT c1, c2, delta = lead(c2, 1, c2) OVER (ORDER BY c1) - c2

    FROM #t1

    ORDER BY c2

    LEAD(c2, n) gives you the nth row ahead of the current one in the result set according to the OVER clause. When there is no row ahead, the third parameter gives you the default to use in place if NULL. The default for n is 1.

    A twin function is LAG which reaches backwards in the result set.

    However, LEAD and LAG are not in SQL 2008, they were added in SQL 2012.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • August 25, 2013 at 10:42 pm

    #1644784

    Thanks, Erland !

    That's exactly I was looking for

    Mario

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply