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

  • Hardy21

    SSCrazy Eights

    Points: 9708

    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:

    EmpId Yr sales TotalSales1 TotalSales2

    1 2005 12000.00 12000.00 12000.00

    1 2006 18000.00 30000.00 30000.00

    1 2007 25000.00 55000.00 55000.00

    1 2008 25000.00 80000.00 68000.00

    1 2009 25000.00 105000.00 75000.00

    2 2005 15000.00 15000.00 15000.00

    2 2006 6000.00 21000.00 21000.00

    2 2007 6000.00 27000.00 27000.00

    Thanks

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Sorry I got the answer. Issue resolved.

    Thanks

  • Jack Corbett

    SSC Guru

    Points: 184381

    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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    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

    😎

  • Hardy21

    SSCrazy Eights

    Points: 9708

    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 5 (of 5 total)

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