Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Find out the new features and enhancements added into SQL Server 2012, codenamed "Denali"

Moderators

Top Categories · View All
TSQL Enhancements 28
Discontinued Features 16
SSIS Enhancements 10
SSMS Enhancements 10
Database Engine 10
Database Administration 8
Security 3
Full Text Search 2
DataTime 1
Breaking Changes 1

Browse by Tags · View All
Denali 82
SQL Server 2012 79
SSIS 9
SSMS Enhancements 9
Analytic Functions 8
Usability Enhancements 5
SQL11 4
TSQL 4
Security 3
Database Engine 3
Datetime 3
Query Hints 2
OVER Clause 2
DMV 2
Groups 2
Dynamic Management Functions 2
DMF 2
System stored procedures 2
DBA 2
FTS 2

Browse by Release · View All
All 58
RC0 20
SQL Server 2012 Denali 2
CTP3 2
SQL Server Denali 1

What is new in SQL Server 2012 - Denali

SQL Server 2012 - OVER Clause is enhanced with ROWS/RANGE Clause to limit the rows

Dec 20 2011 11:19PM by Ramireddy   

In SQL Server 2012, one of the addition to OVER clause is, ROWS/RANGE Clause. In general, when we apply aggregate functions in over clause, it will apply to all rows in that group/partition. Suppose, if we wants to restrict the number of rows, it involves some complexity. Assume, to calculate Next 5yrs total revenue for a company, the number of rows needs to be considered are always that row and next 4 rows.

Now in SQL Server 2012, Over Clause is enhanced with ROWS/RANGE Clause, which allows to specify number of rows to consider while applying aggregates.

Suppose, to calculate sum of last 5 yrs revenue for every row,

ROWS BETWEEN 4 PRECEDING AND CURRENT ROW

Suppose to calculate sum of all preceding years(Cumulative sum),

ROWS  UNBOUNDED PRECEDING

Suppose to calculate sum of next 5yrs revenue for every row

ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING

DECLARE @Companies TABLE
(   
    CompanyId int,
    [Year] int,
    Amount int
)
INSERT INTO @Companies (CompanyId,[Year],[Amount])
VALUES (1,2000,100000),(1,2001,200000),(1,2002,35000),(2,2000,50000),(2,2001,75000),(2,2002,35000)

SELECT  CompanyId,[Year],Amount,
    	SUM(Amount) OVER (Partition by CompanyID 
    						ORDER BY [Year]
    						ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS Next4YrsIncludingCurrentYrRevenue	,
    	SUM(Amount) OVER (Partition by CompanyID 
    						ORDER BY [Year]
    						ROWS  UNBOUNDED PRECEDING
    						) AS CumulativeRevenue,
    	SUM(Amount) OVER (Partition by CompanyID 
    						ORDER BY [Year]
    						ROWS BETWEEN 4 PRECEDING AND CURRENT ROW 
    						) AS Last5YrsRevenue

FROM @Companies



/*
CompanyId   Year        Amount      Next4YrsRevenue CumulativeRevenue Last5YrsRevenue
----------- ----------- ----------- --------------- ----------------- ---------------
1           2000        100000      335000          100000            100000
1           2001        200000      235000          300000            300000
1           2002        35000       35000           335000            335000
2           2000        50000       160000          50000             50000
2           2001        75000       110000          125000            125000
2           2002        35000       35000           160000            160000
*/

Read More Have you written a blog post or article related to this feature and want to get them added here? Read More...
Category : TSQL Enhancements
Release : All
Tags : SQL Server 2012,Denali,TSQL,OVER Clause


Ramireddy
2 · 40% · 12972
4



Submit

2  Comments  

  • It's Good Ram

    but, I have a doubt here the 4th column "Last5YrsRevenue" should be "Next4YrsIncludingCurrentYrRevenue"

    commented on Dec 20 2011 4:44AM
    Shivendra Kumar Yadav
    61 · 3% · 924
  • Shiv,

    Yes you are right. I am updating column name. Thanks for correcting me..

    commented on Dec 20 2011 11:27AM
    Ramireddy
    2 · 40% · 12972

Your Comment


Sign Up or Login to post a comment.

"SQL Server 2012 - OVER Clause is enhanced with ROWS/RANGE Clause to limit the rows" rated 5 out of 5 by 4 readers
SQL Server 2012 - OVER Clause is enhanced with ROWS/RANGE Clause to limit the rows , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]