Skip to content

Instantly share code, notes, and snippets.

@thoolihan
Created October 12, 2022 20:57
Show Gist options
  • Save thoolihan/2f9d2e7b82901631d6da65421114d5b2 to your computer and use it in GitHub Desktop.
Save thoolihan/2f9d2e7b82901631d6da65421114d5b2 to your computer and use it in GitHub Desktop.
WITH cteRevenueDays(Orders, StoreDate, Revenue) AS (
SELECT COUNT(SalesOrderID) AS Orders
,CAST(OrderDate AS DATE) as [StoreDate]
,SUM(SubTotal) AS Revenue
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE DATEPART(YEAR, CAST(OrderDate as DATE)) = 2011
AND DATEPART(MONTH, CAST(OrderDate as DATE)) = 7
GROUP BY CAST(OrderDate as DATE)
) SELECT
StoreDate
,DATEPART(WEEK, StoreDate) AS [StoreWeek]
,Orders
,ROUND(Revenue, 2)
,ROUND(SUM(Revenue) OVER(PARTITION BY DATEPART(WEEK, StoreDate) ORDER BY StoreDate),2) AS WeekToDateRevenue
,ROUND(SUM(Revenue) OVER(ORDER BY StoreDate),2) AS MonthToDateRevenue
FROM cteRevenueDays;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment