CREATE TABLE tst_CustSales (
TCS_ID INT Identity Primary Key Clustered,
TCS_Customer varchar(60),
TCS_Date DATETIME,
TCS_Quantity INT,
TCS_Value MONEY )
;
-- now let's populate our tst_* tables
INSERT tst_CustSales (
TCS_Customer,
TCS_Date,
TCS_Quantity,
TCS_Value
)
SELECT * FROM (
SELECT 'Customer 1' as Customer,'20090101' as Date,
11 as Qty, 1001.00 as Val union all
SELECT 'Customer 1','20090201',12, 1002.00 union all
SELECT 'Customer 1','20090301',13, 1003.00 union all
SELECT 'Customer 1','20090401',14, 1004.00 union all
SELECT 'Customer 2','20090101',21, 2001.00 union all
SELECT 'Customer 2','20090201',22, 2002.00 union all
SELECT 'Customer 2','20090301',23, 2003.00 union all
SELECT 'Customer 2','20090401',24, 2004.00 union all
SELECT 'Customer 3','20090101',31, 3001.00 union all
SELECT 'Customer 4','20090201',42, 4002.00 union all
SELECT 'Customer 5','20090301',53, 5003.00 ) as src
;
For the particulars, please remember to read Dynamic Pivot Procedure for SQL Server.
SELECT TCS_Customer AS Customer
, ISNULL([3], 0.00) AS ThreeMonthsAgoValue
, ISNULL([2], 0.00) AS TwoMonthsAgoValue
, ISNULL([1], 0.00) AS OneMonthAgoValue
, ISNULL([0], 0.00) AS CurrentMonthToDateValue
FROM (
SELECT TCS_Customer
, TCS_Value
, DATEDIFF(MONTH, TCS_Date, GETDATE()) AS TCS_MonthsAgo
FROM tst_CustSales
WHERE TCS_Date >=
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
) b
PIVOT (SUM(TCS_Value)
FOR TCS_MonthsAgo IN ([3],[2],[1],[0])) pvt
;
DATEDIFF(MONTH, TCS_Date, GETDATE())
ISNULL([3], 0.00) AS ThreeMonthsAgoValue
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)