Skip to content

Instantly share code, notes, and snippets.

@tobiascoetzee
Created June 2, 2018 01:02
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Embed
What would you like to do?
SQL query that returns a list of all the products sold with a running total for each product and each day plus a total for each day and product using Windows over function
SET NOCOUNT ON;
SELECT
ProductID
,ModifiedDate
,UnitPrice
,SUM(UnitPrice) OVER (
PARTITION BY ModifiedDate
ORDER BY ProductID, ModifiedDate, SalesOrderID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ASModifiedDateBalance
,SUM(UnitPrice) OVER (
PARTITION BY ProductID
ORDER BY ProductID, ModifiedDate, SalesOrderID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ASProductIdBalance
,SUM(UnitPrice) OVER (
PARTITION BY ModifiedDate, ProductID) AS ModifiedDateTotal
,SUM(UnitPrice) OVER (
PARTITION BY ProductID) AS ProductIdTotal
FROM
Sales.SalesOrderDetail
ORDER BY ProductID, ModifiedDate, SalesOrderID;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment