ROWS clause in the Query [Resolved]

Posted by Sriharim under Sql Server on 8/27/2015 | Points: 10 | Views : 442 | Status : [Member] | Replies : 3
The following example uses the ROWS clause to define a window over which the rows are computed as the current row and the N number of rows that follow (1 row in this example).

SELECT BusinessEntityID, TerritoryID 
,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5;


I didn't understand the use of/how the 'ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING' will work in above query ? Pls explain

---
Srihari



Responses

Posted by: Bandi on: 8/28/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
The OVER clause will consider the current row, and a specific number of rows after the current row.

SELECT empid, salary, SUM(salary) over(order by empid ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) CurrentRowValuePlusNextRowValue
FROM EMPLOYEEs

Empid	Salary	CurrentRowValuePlusNextRowValue 
2 5000.00 12000.00
3 7000.00 13000.00
4 6000.00 8500.00
5 2500.00 4000.00
6 1500.00 1500.00
1st Row calculation: The Current Row + 1 next row means 5000 + 7000 = 12000
2nd Row calculation: The Current Row + 1 next row means 7000 + 6000 = 13000
3rd Row calculation: The Current Row + 1 next row means 6000 + 2500 = 8500
4th Row calculation: The Current Row + 1 next row means 2500 + 1500 = 4000
5th Row calculation: The Current Row + 1 next row means 1500 + No Next Row (i.e. 0) = 1500

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 9/2/2015 [Member] Starter | Points: 25

Up
0
Down
thanxs :-)

---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manub22 on: 9/22/2015 [Member] Starter | Points: 25

Up
0
Down
The option "ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING" that you specified with your OVER clause meant that the Query will calculate the moving SUM of the current row and the next row values.

You can use and change lot of options here, like:
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
- ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING

This also works with RANGE option, like
- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

... and many more.

Check my blog post on the OVER clause and various options with example you can apply with this : http://sqlwithmanoj.com/2013/01/02/enhanced-over-clause-in-sql-server-2012-part2-2/

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response