OVER Clause is enhanced

USE [ExploringSQLR2]
GO

-- OVER Clause is enhanced to apply aggregate functions while applying "Order By"
-- Before SQL Server 2012, We can't use "Order By" clause, while we are using aggregate functions with OVER clause.

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 * FROM @Companies

SELECT  CompanyId,[Year],Amount,
        SUM(Amount) OVER (PARTITION BY CompanyID
                            ORDER BY [Year]) AS CumulativeRevenue        
FROM @Companies


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

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 * FROM @Companies

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