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

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
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)