INNER JOIN [dbo].[DimProductcategory] as c So this is just a running total by sub-category. Since the rows of data are ON c.[ProductCategoryKey]= s.[ProductCategoryKey] by month, there are many rows of data that are in the running total. WHERE YEAR(i.[OrderDate]) = 2008 AND c.ProductCategoryKey = 1 GROUP BY [EnglishProductSubcategoryName], DATEPART(MONTH, i.OrderDate) , DATENAME(MONTH, i.OrderDate) ORDER BY [Subcategory], DATEPART(MONTH, i.OrderDate) USE [AdventureWorksDW2012]; This query adds region which helps to really dig into the analysis. You can use region as a slicer. SELECT [EnglishCountryRegionName] as [Country], [EnglishProductSubcategoryName] AS [SubCategory] , DATEPART(MONTH, rs.OrderDate) AS [Month#] Also with a focus on profit it is very interesting to see , DATENAME(MONTH, rs.OrderDate) AS [Month] that sales are often pushed at the expense of profit. , COUNT(rs.[OrderQuantity]) AS [Monthly Qty] , SUM(rs.[SalesAmount])AS [Monthly Revenue] , SUM([SalesAmount] - [TotalProductCost]) as [Profit] Sure it’s good to keep the factory working, but if we are /* Here a running total of the [SalesAmount] column is totaled for losing money in some regions, it’s time to take a closer each month in each product subcategory group. Notice the running look, and make some changes. total resets for each change in ProductSubcategory. The fields in the partition by are some (not necessarily all) the fields in the GROUP BY */ What are the reasons to have good sales amounts yet ,SUM(SUM([SalesAmount])) OVER lose money? (PARTITION BY [EnglishCountryRegionName], [EnglishProductSubcategoryName] ORDER BY DATEPART(MONTH, rs.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Revenue Running Total]
INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey] WHERE YEAR(i.[OrderDate]) = 2008 AND c.ProductCategoryKey = 1 GROUP BY [EnglishProductSubcategoryName], DATEPART(MONTH, i.OrderDate) , DATENAME(MONTH, i.OrderDate) ORDER BY [Subcategory], DATEPART(MONTH, i.OrderDate) So this is just a running total by sub-category. Since the rows of data are by month, there are many rows of data that are in the running total. USE [AdventureWorksDW2012]; SELECT [EnglishCountryRegionName] as [Country], [EnglishProductSubcategoryName] AS [SubCategory] , DATEPART(MONTH, rs.OrderDate) AS [Month#] , DATENAME(MONTH, rs.OrderDate) AS [Month] , COUNT(rs.[OrderQuantity]) AS [Monthly Qty] , SUM(rs.[SalesAmount])AS [Monthly Revenue] , SUM([SalesAmount] - [TotalProductCost]) as [Profit] /* Here a running total of the [SalesAmount] column is totaled for each month in each product subcategory group. Notice the running total resets for each change in ProductSubcategory. The fields in the partition by are some (not necessarily all) the fields in the GROUP BY */ ,SUM(SUM([SalesAmount])) OVER (PARTITION BY [EnglishCountryRegionName], [EnglishProductSubcategoryName] ORDER BY DATEPART(MONTH, rs.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Revenue Running Total] This query adds region which helps to really dig into the analysis. You can use region as a slicer. Also with a focus on profit it is very interesting to see that sales are often pushed at the expense of profit. Sure it’s good to keep the factory working, but if we are losing money in some regions, it’s time to take a closer look, and make some changes. What are the reasons to have good sales amounts yet lose money?
Feathermans-TSQL-Analytics-Examples-Window-Functions-1-4-2017.docx