Recently, I was asked to help out in implementing a requirement for a reporting application. The application was designed to report weekly consumption trends from the day the report is run, and we therefore had to generate a lookup with week long date ranges from any given day.

For example, if the report is run from November 30, 2015, the week ends on December 06, 2015 and subsequent weeks are at 7 days intervals from that point on. If the report is run on December 02, 2015, the first week ends on December 08, 2015 and so on.

In this post, I present two scripts that I created as PoC for the implementation.

Script #01

The first script uses a simple approach – it basically runs a WHILE loop starting from the start date supplied to the script and keeps adding 7 days to the date supplied as the start date. This loop continues until the derived start date exceeds the end date supplied for generating the lookup.

USE tempdb;
GO
DECLARE @weekStartDate DATE = '2015-12-02';
DECLARE @weekEndDate DATE = '2016-02-08';
DECLARE @iterationDate DATE = @weekStartDate;
DECLARE @weekNumberIterator INT = 1;

DECLARE @weekDay TABLE (RelativeWeekNumber INT  NOT NULL,
                        WeekStartDate      DATE NOT NULL,
                        WeekEndDate        DATE NOT NULL
                       );

--Generator
WHILE DATEDIFF(DAY,@iterationDate,@weekEndDate) > 0
BEGIN
    INSERT INTO @weekDay (RelativeWeekNumber, WeekStartDate, WeekEndDate)
    SELECT @weekNumberIterator             AS RelativeWeekNumber,
           @iterationDate                  AS WeekStartDate,
           DATEADD(DAY, 6, @iterationDate) AS WeekEndDate;     --Week will end 6 days AFTER start date

    SELECT @iterationDate = DATEADD(DAY, 7, @iterationDate),   --New week will start 7 days AFTER start date
           @weekNumberIterator += 1;                           --The relative week number is a simple iterator
END

SELECT wd.RelativeWeekNumber,
       wd.WeekStartDate,
       wd.WeekEndDate
FROM @weekDay AS wd;
GO
/*****************
**** RESULTS *****
*****************/
RelativeWeekNumber WeekStartDate WeekEndDate
------------------ ------------- -----------
1                  2015-12-02    2015-12-08
2                  2015-12-09    2015-12-15
3                  2015-12-16    2015-12-22
4                  2015-12-23    2015-12-29
5                  2015-12-30    2016-01-05
6                  2016-01-06    2016-01-12
7                  2016-01-13    2016-01-19
8                  2016-01-20    2016-01-26
9                  2016-01-27    2016-02-02
10                 2016-02-03    2016-02-09

(10 row(s) affected)

Script #02

This version uses a recursive CTE to achieve the same result.

-----Alternate Logic, using Recursive CTE
DECLARE @recursiveWeekStartDate DATE = '2015-12-02';
DECLARE @recursiveWeekEndDate DATE = '2016-02-08';

;WITH WeekPatternGenerator (RelativeWeekNumber, WeekStartDate, WeekEndDate)
AS (--Anchor Member
    SELECT 1 AS RelativeWeekNumber,
           @recursiveWeekStartDate AS WeekStartDate,
           DATEADD(DAY, 6, @recursiveWeekStartDate)  AS WeekEndDate
    UNION ALL
    --Recursive Member
    SELECT (wpg.RelativeWeekNumber + 1) AS RelativeWeekNumber,
           DATEADD(DAY,7,wpg.WeekStartDate) AS WeekStartDate,
           DATEADD(DAY,6,DATEADD(DAY,7,wpg.WeekStartDate)) AS WeekEndDate
    FROM WeekPatternGenerator AS wpg
    WHERE wpg.WeekStartDate < DATEADD(DAY,-6,@recursiveWeekEndDate)
  )
SELECT wpgCTE.RelativeWeekNumber,
       wpgCTE.WeekStartDate,
       wpgCTE.WeekEndDate
FROM WeekPatternGenerator AS wpgCTE;
GO
/*****************
**** RESULTS *****
*****************/
RelativeWeekNumber WeekStartDate WeekEndDate
------------------ ------------- -----------
1                  2015-12-02    2015-12-08
2                  2015-12-09    2015-12-15
3                  2015-12-16    2015-12-22
4                  2015-12-23    2015-12-29
5                  2015-12-30    2016-01-05
6                  2016-01-06    2016-01-12
7                  2016-01-13    2016-01-19
8                  2016-01-20    2016-01-26
9                  2016-01-27    2016-02-02
10                 2016-02-03    2016-02-09

(10 row(s) affected)

The Reverse – Requirement to find weekdays between two dates

In order to validate the bigger sets of date ranges, one can use the approaches documented in the post [Interview Question of the Week #047 – Logic To Find Weekdays Between Two Dates] by Pinal Dave [B|T].

Until we meet next time,
Be courteous. Drive responsibly.