Execute the following script in Query Editor to create a stored procedure which generates total orders by 4 moving quarters.

use AdventureWorks2008;

go

create proc OrdersByMovingQuarters @EndDate datetime

as

begin

declare @Quarters table (Quarter varchar(20), [Begin] datetime, [End] datetime)

insert @Quarters select 'Q4', dateadd(mm,-3, @EndDate), @EndDate

insert @Quarters select 'Q3', dateadd(mm,-6, @EndDate), dateadd(mm,-3, @EndDate)

insert @Quarters select 'Q2', dateadd(mm,-9, @EndDate), dateadd(mm,-6, @EndDate)

insert @Quarters select 'Q1', dateadd(mm,-12, @EndDate), dateadd(mm,-9, @EndDate)

select Quarter, ItemOrders=count(*)

from AdventureWorks.Sales.SalesOrderHeader soh

join AdventureWorks.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

exec OrdersByMovingQuarters '2004-03-31'