Post reply

Difference between ROWS UNBOUNDED PRECEDING And ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

  • July 14, 2014 at 6:46 am

    #296443

    Hello,

    What's difference between 'ROWS UNBOUNDED PRECEDING' And 'ROWS BETWEEN 2 PRECEDING AND CURRENT ROW'?

    In below set of data, how '75,000' is calculated as TotalSales2 for EmpId. 1 and year 2009 (line no 5)?

    DECLARE @salesData TABLE

    (EmpId INT, Yr INT, Sales DECIMAL(8,2))

    INSERT INTO @salesData ( EmpId, Yr, Sales )

    VALUES (1, 2005, 12000),

    (1, 2006, 18000),

    (1, 2007, 25000),

    (1, 2008, 25000),

    (1, 2009, 25000),

    (2, 2005, 15000),

    (2, 2006, 6000),

    (2, 2007, 6000)

    SELECT EmpId, Yr, sales,

    SUM(Sales) OVER (PARTITION BY empid ORDER BY empid ROWS UNBOUNDED PRECEDING ) AS TotalSales1,

    SUM(Sales) OVER (PARTITION BY empid ORDER BY empid ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS TotalSales2

    FROM @salesData

    output:

    EmpIdYrsalesTotalSales1TotalSales2

    1200512000.0012000.0012000.00

    1200618000.0030000.0030000.00

    1200725000.0055000.0055000.00

    1200825000.0080000.0068000.00

    1200925000.00105000.0075000.00

    2200515000.0015000.0015000.00

    220066000.0021000.0021000.00

    220076000.0027000.0027000.00

    Thanks

  • July 14, 2014 at 6:48 am

    #1729119

    Sorry I got the answer. Issue resolved.

    Thanks

  • July 14, 2014 at 10:24 am

    #1729194

    Hardy21 (7/14/2014)

    Sorry I got the answer. Issue resolved.

    And the answer is?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 14, 2014 at 1:43 pm

    #1729259

    The answer depends, the default framing of a window function varies depending on the function. As an example, the first_value function has the default framing of rows between unbounded preceding (all previous) and unbounded following (all subsequent) rows, while last_value has the default framing of unbounded preceding and current row. Not to be mixed with the equal syntax for range either.

    As an example, a running total would be calculated with the framing of unbounded preceding and current row, if it was set to unbounded preceding and unbounded following, it would be the default framing for a total sum for the partition. Let me know if I'm muddling the water here:-P

    😎

  • July 14, 2014 at 10:33 pm

    #1729317

    Eirikur Eiriksson (7/14/2014)

    The answer depends, the default framing of a window function varies depending on the function. As an example, the first_value function has the default framing of rows between unbounded preceding (all previous) and unbounded following (all subsequent) rows, while last_value has the default framing of unbounded preceding and current row. Not to be mixed with the equal syntax for range either.

    As an example, a running total would be calculated with the framing of unbounded preceding and current row, if it was set to unbounded preceding and unbounded following, it would be the default framing for a total sum for the partition. Let me know if I'm muddling the water here:-P

    😎

    That's correct 😛

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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