DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to aggregate by moving quarters?

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

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE