|
Execute the following
script in Query Editor to generate a monthly sales summary by categories. Since gap filling used, $0.0 will show for a month when there was no sale.
USE AdventureWorks;
GO
CREATE PROCEDURE procSalesDatawithGapFills (
@StartDate smalldatetime,
@EndDate smalldatetime
)
AS
BEGIN
DECLARE @RunningDate smalldatetime
DECLARE @CategoriesMonths TABLE (
ProductCategoryID tinyint,
OrderDate smalldatetime
)
SET @RunningDate = @EndDate
-- Insert a single date from each month in the range
WHILE (@StartDate <= @RunningDate)
BEGIN
INSERT INTO @CategoriesMonths
SELECT PC.ProductCategoryID, @RunningDate
FROM Production.ProductCategory PC
SET @RunningDate = DATEADD(mm, -1, @RunningDate)
END;
-- Apply a CTE to merge the place holder and available data
WITH cteSales
AS
(
-- Place holder sales data ($0.00) just in case no real data
SELECT
DS.ProductCategoryID,
PC.Name as ProductCategory,
OrderDate AS Date,
0.0 AS Sales
FROM @CategoriesMonths DS
INNER JOIN Production.ProductCategory PC
ON DS.ProductCategoryID=PC.ProductCategoryID
UNION ALL
-- Merge in the available data
SELECT
PC.ProductCategoryID,
PC.Name AS ProductCategory,
SOH.OrderDate AS [Date],
SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales
FROM Production.ProductSubCategory PSC
INNER JOIN Production.ProductCategory PC
ON PSC.ProductCategoryID = PC.ProductCategoryID
INNER JOIN Production.Product P
ON PSC.ProductSubCategoryID = P.ProductSubCategoryID
INNER JOIN Sales.SalesOrderDetail SOD
ON P.ProductID = SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE (SOH.OrderDate BETWEEN @StartDate AND @EndDate)
GROUP BY SOH.OrderDate, PC.Name, PSC.Name, PC.ProductCategoryID
)
-- Generate summaries for each month, each category
SELECT [Year]=Year([Date]),
[Month]=Month([Date]),
ProductCategory,
TotalSales = Sum(Sales)
FROM cteSales
GROUP BY Year([Date]),Month([Date]), ProductCategory
ORDER BY Year([Date]),Month([Date]), ProductCategory
END
GO
-- Test the stored procedure
exec procSalesDatawithGapFills '2001-01-01','2002-04-30'
GO |
