age buckets and different as of dates?

do you mean this?

set dateformat mdy
go

DECLARE @CurrentQuarter DATE = '3/31/2012'
DECLARE @SecondQuarter DATE , @ThirdQuarter DATE
SELECT @SecondQuarter = DATEADD(Q, -1, @CurrentQuarter), @ThirdQuarter = DATEADD(Q, DATEDIFF(Q,0,@CurrentQuarter)-2,0)
SELECT @SecondQuarter, @ThirdQuarter
DECLARE @Temp TABLE (Hotel_Name INT, Guest INT, Service_Date DATE, Post_Date DATE, Amount FLOAT)
INSERT INTO @Temp
SELECT 112, 991, '1/1/2011', '2/1/2011', 150
UNION ALL
SELECT 113, 992, '1/2/2011', '2/10/2011', 100
UNION ALL
SELECT 114, 993, '2/2/2011', '4/20/2011', 100
UNION ALL
SELECT 115, 994, '3/2/2011', '4/15/2011', 200
UNION ALL
SELECT 116, 995, '5/2/2011', '6/30/2011', 300
UNION ALL
SELECT 112, 991, '2/2/2012', '3/1/2012', 150
 
SELECT CASE WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  0 AND 3 THEN '0-3'
                     WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  4 AND 6 THEN '4-6'
                     WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  7 AND 9 THEN '7-9'          
                     WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  10 AND 12 THEN '10-12'         
                     WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  13 AND 15 THEN '13-15'         
          END AS Age_Bucket,
                SUM(CASE WHEN Post_Date >= DATEADD(Q, DATEDIFF(Q,0,@CurrentQuarter),0) AND Post_Date < DATEADD(DD,1,@CurrentQuarter)  THEN Amount ELSE 0 END) AS Amount_AsOf_Q1,
                SUM(CASE WHEN Post_Date >= DATEADD(Q, DATEDIFF(Q,0,@CurrentQuarter)-1,0) AND Post_Date < DATEADD(Q, DATEDIFF(Q,0,@CurrentQuarter),0) THEN Amount ELSE 0 END) AS Amount_AsOf_Q2,
                SUM(CASE WHEN Post_Date >= DATEADD(Q, DATEDIFF(Q,0,@CurrentQuarter)-2,0) AND Post_Date < DATEADD(Q, DATEDIFF(Q,0,@CurrentQuarter)-1,0) THEN Amount ELSE 0 END) AS Amount_AsOf_Q3
FROM @Temp
WHERE Post_Date >= @ThirdQuarter
AND Post_Date < DATEADD(dd,1,@CurrentQuarter)
GROUP BY  CASE WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  0 AND 3 THEN '0-3'
                     WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  4 AND 6 THEN '4-6'
                     WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  7 AND 9 THEN '7-9'          
                     WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  10 AND 12 THEN '10-12'         
                     WHEN DATEDIFF(mm,Service_Date, @CurrentQuarter) between  13 AND 15 THEN '13-15'         
          END

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c