|
Execute the following
Microsoft SQL Server T-SQL script in Query Editor to create a stored procedure which generates total order details by 4 moving quarters.
-- T-SQL moving quarters basic syntax - sql 12 month moving average
DECLARE @EndOfQuarterDate date = '2004-03-31'
SELECT YEAR = YEAR(OrderDate),
QUARTER = DATEPART(QQ,OrderDate),
SalesOrderDetailCount = count(* ),
AvgLineTotal = convert(varchar,convert(money,AVG(LineTotal)),1)
FROM AdventureWorks2008.Sales.SalesOrderHeader soh
INNER JOIN AdventureWorks2008.Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE OrderDate <= @EndOfQuarterDate
AND OrderDate > DATEADD(yy,-1,@EndOfQuarterDate)
GROUP BY YEAR(OrderDate),
DATEPART(QQ,OrderDate)
ORDER BY YEAR(OrderDate) DESC,
QUARTER DESC
/*
YEAR QUARTER SalesOrderDetailCount AvgLineTotal
2004 1 19563 582.65
2003 4 21346 622.66
2003 3 18838 726.29
2003 2 6403 1,305.31
*/
-- Create stored procedure for moving quarters reporting
USE AdventureWorks2008;
GO
CREATE PROC OrdersByMovingQuarters
@EndDate DATE -- Must be end of quarter
AS
BEGIN
DECLARE @Quarters TABLE(
Quarter VARCHAR(20),
[Begin] DATE,
[End] DATE
)
INSERT @Quarters
SELECT 'Q0 This',
dateadd(mm,-3,@EndDate),
@EndDate
INSERT @Quarters
SELECT 'Q1 Last',
dateadd(mm,-6,@EndDate),
dateadd(mm,-3,@EndDate)
INSERT @Quarters
SELECT 'Q2 Last-',
dateadd(mm,-9,@EndDate),
dateadd(mm,-6,@EndDate)
INSERT @Quarters
SELECT 'Q3 Last--',
dateadd(mm,-12,@EndDate),
dateadd(mm,-9,@EndDate)
SELECT Quarter,
ItemOrders = count(* )
FROM AdventureWorks2008.Sales.SalesOrderHeader soh
JOIN AdventureWorks2008.Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
JOIN @Quarters
ON OrderDate > [Begin] AND OrderDate <= [End]
GROUP BY Quarter
ORDER BY Quarter
END
GO
-- Test stored procedure
EXEC OrdersByMovingQuarters '2004-03-31'
/*
Quarter ItemOrders
Q0 This 19563
Q1 Last 21346
Q2 Last- 18838
Q3 Last-- 6403
*/
|
