SQL Common Table Expression (CTE) Date Ranges with recursive Transact SQL Queries

Now often I've got to product different date ranges of reports (ok always), such as quarterly, monthly, weekly (starting on a Monday!) and by Financial Year.

With application programmers liking to build massive amounts of code to loop through dates and repeatedly query the database where as I like to be able to see the data before I design any reports, I was always copy pasting a CTE query template I had for day by day, month by month etc reports.

After losing my template, I decided to write the template as a function return a table data type, which then lets me just select the function in my query like so;


select vdr.theRangeStart, vdr.theRangeEnd
from viewDateRange('MONTH', '20121105', '20130605') vdr

Note that the results of this look odd at the start and the end; which is by design;

theRangeStart        theRangeEnd
2012-11-05 00:00:00.000    2012-12-01 00:00:00.000
2012-12-01 00:00:00.000    2013-01-01 00:00:00.000
2013-01-01 00:00:00.000    2013-02-01 00:00:00.000
2013-02-01 00:00:00.000    2013-03-01 00:00:00.000
2013-03-01 00:00:00.000    2013-04-01 00:00:00.000
2013-04-01 00:00:00.000    2013-05-01 00:00:00.000
2013-05-01 00:00:00.000    2013-06-01 00:00:00.000
2013-06-01 00:00:00.000    2013-06-06 00:00:00.000


Typically users enter a date and want that date included, therefore I've made sure that the last "theRangeEnd" value is midnight (essentially the next day). You may encounter issues there if your datefield you are comparing to is a "date" only field depending on your comparison operators by using less than or less than or equal (I warned you).

Also, as I have different date formats to deal with, I decided to just send in a date as a string format YYYYMMDD rather than deal with dd/mm/yyyy and mm/dd/yyyy formats.


create function [dbo].[viewDateRange] (@rangeStyle varchar(10), @fromDate varchar(10) , @toDate varchar(10) )
-- eg. select * from viewDateRange('DAY', '01/11/2014', '01/02/2015') order by 1
returns @daterange
table (
    theRangeStart datetime
    , theRangeEnd datetime
    , dayOfWeekAsString varchar(20)
    , numberOfDays integer
    )
as
begin

/*
Example Usage is
select * from viewDateRange('DAY', '20141102', '20150131')
select * from viewDateRange('WEEK', '20141105', '20150131')
select * from viewDateRange('MONTH', '20141105', '20141105')
select * from viewDateRange('FINYEAR', '20111105', '20150131')
select * from viewDateRange('QUARTER', '20141105', '20150131')

left join it with the table you want to look at!

*/
-- query comes back on the basis of a day comparison so between for the month 1/jan/2015 to 31/Jan/2015 (typical user input), the query would come back
-- with  2015-01-26 00:00:00.000    2015-02-01 00:00:00.000 so and date comparison can be when date between theRangeStart and theRangeEnd
declare @dttmfrom datetime = convert(datetime,  @fromDate , 112), @dttmTo datetime = convert(datetime,  @toDate , 112)

begin

if @rangeStyle = 'DAY'
        with qryDates as (
            select  @dttmFrom theRangeStart
                , dateadd(d, 1, @dttmFrom) theRangeEnd
            union all
            select dateadd(d, 1, theRangeStart) theRangeStart
                , dateadd(d, 1, theRangeEnd) theRangeEnd
            from qryDates
            where dateadd(d, +1, theRangeStart) <=  @dttmTo
        )
        insert @daterange
        select theRangeStart, theRangeEnd, dbo.udf_DayOfWeek(theRangeStart) theWeekDay, datediff(d, theRangeStart, theRangeEnd) numberOfDays
        from qryDates

else if @rangeStyle = 'MONTH'
        -- first month could be a short month if date 1st of month
        -- same last month could be a short month if not a full month

        with qryDates as (
            select  @dttmFrom theRangeStart
                , dateadd(mm, 1, dateadd(day, 1- datepart(day, @dttmFrom),@dttmFrom)    ) theRangeEnd
            union all
            select dateadd(mm, 1, dateadd(day, 1- datepart(day, theRangeStart),theRangeStart)    ) theRangeStart
                , case when @dttmTo between dateadd(mm, 1, dateadd(day, 1- datepart(day, theRangeStart),theRangeStart) ) and  dateadd(mm, 1, theRangeEnd) then dateadd(d, 1, @dttmTo)  else dateadd(mm, 1, theRangeEnd) end theRangeEnd
            from qryDates
            where theRangeEnd <  @dttmTo
        )
        insert @daterange
        select theRangeStart, theRangeEnd, dbo.udf_DayOfWeek(theRangeStart) theWeekDay, datediff(d, theRangeStart, theRangeEnd) numberOfDays
        from qryDates


else if @rangeStyle = 'WEEK'
        -- first week could be a short week if date is not a monday
        -- same last week could be a short week if not a full month

        with qryDates as (
            select  @dttmFrom theRangeStart
                , dateadd(wk, 1, dateadd(day, 3 - datepart(day, @dttmFrom),@dttmFrom)    ) theRangeEnd -- Start week on Monday
            union all
            select dateadd(wk, 1, dateadd(day, 2 - datepart(weekday, theRangeStart),theRangeStart)) theRangeStart-- Start week on Monday
                , case when @dttmTo between dateadd(wk, 1, dateadd(day, 1- datepart(day, theRangeStart),theRangeStart)    ) and  dateadd(wk, 1, theRangeEnd) then dateadd(d, 1, @dttmTo) else dateadd(wk, 1, theRangeEnd) end theRangeEnd
            from qryDates
            where theRangeEnd <  @dttmTo
        )
        insert @daterange
        select theRangeStart, theRangeEnd, dbo.udf_DayOfWeek(theRangeStart) theWeekDay, datediff(d, theRangeStart, theRangeEnd) numberOfDays
        from qryDates

else if @rangeStyle = 'QUARTER'
        -- first quarter could be a short week if date is not a monday
        -- same last quarter could be a short week if not a full month
        -- calculated using normal quarters of Jan to Mar etc
        with qryDates as (
            select  @dttmFrom theRangeStart
                , dateadd(m, 3, dateadd(d, -datepart(d,  dateadd(m, -(datepart(m, @dttmFrom) % 3) , @dttmFrom) ) + 1 , dateadd(m, -(datepart(m, @dttmFrom) % 3)+ 1 , @dttmFrom) ))  theRangeEnd
            union all
            select dateadd(m, 3, dateadd(d, -datepart(d,  dateadd(m, -(datepart(m, theRangeStart) % 3) , theRangeStart) ) + 1 , dateadd(m, -(datepart(m, theRangeStart) % 3)+ 1 , theRangeStart) )) theRangeStart
                , case when @dttmTo between dateadd(qq, 1, dateadd(day, 1- datepart(day, theRangeStart),theRangeStart)    ) and  dateadd(qq, 1, theRangeEnd) then dateadd(d, 1, @dttmTo)  else dateadd(qq, 1, theRangeEnd) end theRangeEnd
            from qryDates
            where theRangeEnd <  @dttmTo
        )
        insert @daterange
        select theRangeStart, theRangeEnd, dbo.udf_DayOfWeek(theRangeStart) theWeekDay, datediff(d, theRangeStart, theRangeEnd) numberOfDays
        from qryDates


else if @rangeStyle = 'FINYEAR'
        -- first quarter could be a short week if date is not a monday
        -- same last quarter could be a short week if not a full month
        -- calculated using normal quarters of Jan to Mar etc
        with qryDates as (
            select theRangeStart,
                    case when datepart(m, addyear) >= 7 then dateadd(m, -(datepart(m, addyear) % 6 ) + 1, addyear)
                    else dateadd(m, (datepart(m, addyear) % 6 ) + 5, addyear)
                    end theRangeEnd 
            from
            (
                select theRangeStart, dateadd(d, - datepart(day, addYear) + 1, addYear) addyear
                from (
                    select  @dttmFrom theRangeStart, dateadd(yyyy, 1, @dttmFrom) addYear
                ) x
            ) x
            union all
            select    theRangeEnd theRangeStart
                    , case when @dttmTo between theRangeStart and  dateadd(yyyy, 1, theRangeEnd  ) then dateadd(d, 1, @dttmTo)  else dateadd(yyyy, 1, theRangeEnd  ) end theRangeEnd
                   
            from qryDates
            where theRangeend < @dttmTo
           
        )
        insert @daterange
        select theRangeStart, theRangeEnd, dbo.udf_DayOfWeek(theRangeStart) theWeekDay, datediff(d, theRangeStart, theRangeEnd) numberOfDays
        from qryDates


return
end
end