Expert T-SQL Window Functions in SQL Server by Kathi Kellenberger & Clayton Groom

Author:Kathi Kellenberger & Clayton Groom
Language: eng
Format: epub
Publisher: Apress, Berkeley, CA

Run Listing 5-2 to see an example.

Listing 5-2. Calculating Moving Averages and Sums

--5-2.1 Three month sum and average for products qty sold

SELECT MONTH(SOH.OrderDate) AS OrderMonth, SOD.ProductID, SUM(SOD.OrderQty) AS QtySold,

SUM(SUM(SOD.OrderQty))

OVER(PARTITION BY SOD.ProductID ORDER BY MONTH(SOH.OrderDate)

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ThreeMonthSum,

AVG(SUM(SOD.OrderQty))

OVER(PARTITION BY SOD.ProductID ORDER BY MONTH(SOH.OrderDate)

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ThreeMonthAvg

FROM Sales.SalesOrderHeader AS SOH

JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID

JOIN Production.Product AS P ON SOD.ProductID = P.ProductID

WHERE OrderDate >= '2013-01-01' AND OrderDate < '2014-01-01'

GROUP BY MONTH(SOH.OrderDate), SOD.ProductID;

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.