Date Range

Most of us come across the situation, where we need to execute the hour/day/week/ wise reports. we do simple group by clause and publish it .. but what if table does not have data for all the hours in a day or all the days in month or all the weeks in month or year, this case most of the people do it in while loop :) in fact I did for couple of instances, but any more now. How I do now.. here we go ...

Take the advantage of CTE.. Here is the Inline

 Declare @startdate datetime

,@Increment varchar(5)= 'D' --H = Hour,d=day, w= Week , m= Month

,@EndDate DATETIME

DECLARE @SelectedRange TABLE

(IndividualDate DATETIME)

select @startdate = GETDATE()-30

SELECT @EndDate = GETDATE()

;WITH cteRange (DateRange) AS (

SELECT @StartDate

UNION ALL

SELECT

CASE

WHEN @Increment = 'd' THEN DATEADD(HH, 1, DateRange)

WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)

WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)

WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)

END

FROM cteRange

WHERE DateRange <=

CASE

WHEN @Increment = 'd' THEN DATEADD(HH, -1, @EndDate)

WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)

WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)

WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)

END)

INSERT INTO @SelectedRange (IndividualDate)

SELECT DateRange

FROM cteRange

OPTION (MAXRECURSION 3660);

SELECT * FROM @SelectedRange