Microsoft® SQL Server® 2012 Bible

268

Part II: Building Databases and Working with Data

USE AdventureWorks

GO

WITH YearlyCountryRegionSales

AS

(

SELECT

[Group] AS CtryReg,

Year(soh.OrderDate) OrYr,

SUM(TotalDue) TotalDueYTD

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesTerritory st

ON soh.TerritoryID = st.TerritoryID

GROUP BY

[Group], Year(soh.OrderDate)

)

SELECT

CtryReg,

OrYr,

TotalDueYTD CurrentYearTotals,

SUM(TotalDueYTD)

OVER( PARTITION BY CtryReg ORDER BY OrYr

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) - TotalDueYTD

PreviousYearTotals,

SUM(TotalDueYTD)

OVER( PARTITION BY CtryReg ORDER BY OrYr

ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) - TotalDueYTD

NextYearTotals

FROM YearlyCountryRegionSales

Results:

CtryReg OrYr CurrentYearTotals PreviousYearTotals NextYearTotals

------- ---- ----------------- ----------------- ----------------

Europe 2005 784491.6708 0.00 3713564.3139

Europe 2006 3713564.3139 784491.6708 10120806.8258

Europe 2007 10120806.8258 3713564.3139 7554754.8192

Europe 2008 7554754.8192 10120806.8258 0.00

North America 2005 10462261.7812 0.00 28369799.2827

North America 2006 28369799.2827 10462261.7812 32737387.8837

North America 2007 32737387.8837 28369799.2827 17659343.4434

North America 2008 17659343.4434 32737387.8837 0.00

Pacific 2005 1446497.1744 0.00 2380484.8387

Pacific 2006 2380484.8387 1446497.1744 4313294.8365

Pacific 2007 4313294.8365 2380484.8387 3674099.2456

Pacific 2008 3674099.2456 4313294.8365 0.00

As seen in the preceding results, the PreviousYearTotals column represents the value

of the previous year totals, and the NextYearTotals column represents the value of the

c10.indd 268c10.indd 268 7/30/2012 4:26:14 PM7/30/2012 4:26:14 PM

http://www.it-ebooks.info