SQL Calendar Table based on Start and End Date Help

  • Hello Everyone,

    I need some help in setting up a Calendar Table or an Staging Table that I can use for running some reports.

    I'm given a Start Date and an End Date and I'm trying to return a table that has weeks with Start Date and End Date within the two given dates, the weeks starts on Sundays and ends on Mondays.

    After doing research on the web I found the following SQL query that gives me the starting point by separating the weeks out with Start Dates (Sundays) however I also need the end dates (Mondays).

    Please help me get the desired results.

    Thank you,

    Below is the code I'm using:

    DECLARE @STARTDATE DATETIME,

    @ENDDATE DATETIME

    SET @STARTDATE ='06/05/2016'

    SET @ENDDATE = '07/02/2016'

    ;

    WITH CTE_DatesTable

    AS

    (

    SELECT CAST(@STARTDATE as datetime) AS [date]

    UNION ALL

    SELECT DATEADD(WEEK, 1, [date])

    FROM CTE_DatesTable

    WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE

    )

    SELECT [date] FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

    Current Results

    STARTDATE

    2016-06-05 00:00:00.000

    2016-06-12 00:00:00.000

    2016-06-19 00:00:00.000

    2016-06-26 00:00:00.000

    Desired Results

    STARTDATE ENDDATE WEEK

    2016-06-05 00:00:00.000 2016-06-11 00:00:00.000 1

    2016-06-12 00:00:00.000 2016-06-18 00:00:00.000 2

    2016-06-19 00:00:00.000 2016-06-25 00:00:00.000 3

    2016-06-26 00:00:00.000 2016-07-02 00:00:00.000 4

  • Quick suggestion: you can add 6 days and you will get the end date as well.

    SELECT [date] as StartDate, DATEADD(dd,6,[date]) as EndDate

    FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

  • twin.devil (6/13/2016)


    Quick suggestion: you can add 6 days and you will get the end date as well.

    SELECT [date] as StartDate, DATEADD(dd,6,[date]) as EndDate

    FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

    twin.devel nailed it, and you can simply add a row_number function to generate your week.

    SELECT

    [date] as StartDate,

    DATEADD(dd,6,[date]) as EndDate,

    row_number() over(ORDER BY [date]) AS [Week]

    FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • For a work-table free, sort-free, purely set-based way of handling this you could use getnumsAB[/url] and do this:

    DECLARE @STARTDATE DATETIME = '06/05/2016',

    @ENDDATE DATETIME = '07/02/2016';

    SELECT STARTDATE = DATEADD(DAY,n1,@STARTDATE),

    ENDDATE = DATEADD(DAY,n2,@STARTDATE) - 1,

    [WEEK] = rn

    FROM GetNumsAB(0, DATEDIFF(DAY,@STARTDATE,@ENDDATE), 7, 1);

    Edit: further simplified the solution.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • DiabloSlayer (6/13/2016)


    Hello Everyone,

    I need some help in setting up a Calendar Table or an Staging Table that I can use for running some reports.

    I'm given a Start Date and an End Date and I'm trying to return a table that has weeks with Start Date and End Date within the two given dates, the weeks starts on Sundays and ends on Mondays.

    After doing research on the web I found the following SQL query that gives me the starting point by separating the weeks out with Start Dates (Sundays) however I also need the end dates (Mondays).

    Please help me get the desired results.

    Thank you,

    Below is the code I'm using:

    DECLARE @STARTDATE DATETIME,

    @ENDDATE DATETIME

    SET @STARTDATE ='06/05/2016'

    SET @ENDDATE = '07/02/2016'

    ;

    WITH CTE_DatesTable

    AS

    (

    SELECT CAST(@STARTDATE as datetime) AS [date]

    UNION ALL

    SELECT DATEADD(WEEK, 1, [date])

    FROM CTE_DatesTable

    WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE

    )

    SELECT [date] FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

    Current Results

    STARTDATE

    2016-06-05 00:00:00.000

    2016-06-12 00:00:00.000

    2016-06-19 00:00:00.000

    2016-06-26 00:00:00.000

    Desired Results

    STARTDATE ENDDATE WEEK

    2016-06-05 00:00:00.000 2016-06-11 00:00:00.000 1

    2016-06-12 00:00:00.000 2016-06-18 00:00:00.000 2

    2016-06-19 00:00:00.000 2016-06-25 00:00:00.000 3

    2016-06-26 00:00:00.000 2016-07-02 00:00:00.000 4

    Last query is my take. eliminates the recursive counting which will not scale well over larger values.

    D E C L A R E -- spaced to permit posting from current location

    @STARTDATE DATETIME,

    @ENDDATE DATETIME;

    SET @STARTDATE ='06/05/2016';

    SET @ENDDATE = '07/02/2016';

    WITH CTE_DatesTable

    AS

    (

    SELECT CAST(@STARTDATE as datetime) AS [date]

    UNION ALL

    SELECT DATEADD(WEEK, 1, [date])

    FROM CTE_DatesTable

    WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE

    )

    SELECT [date] FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

    with

    e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

    e2(n) as (select 1 from e1 a cross join e1 b),

    e4(n) as (select 1 from e2 a cross join e2 b),

    eTally(n) as (select top(datediff(week,@STARTDATE,@ENDDATE) + 1) rn = row_number() over (order by (select null)) - 1 from e4)

    select

    dateadd(week,n,@STARTDATE) STARTDATE,

    dateadd(day,-1,dateadd(week,n + 1,@STARTDATE)) ENDDATE,

    n + 1 [WEEK]

    from

    eTally;

  • Thanks to both twin.devil and Lowell for the quick solution, that's exactly the output I was looking for and presented to the client however ..

    The client now added a twist to the requirements this morning by saying that the Start Date should be from beginning of the month till the end of the month, for instance for June it would be from 6/1 thru 6/30.

    In this case the SQL query that I have will not work to get the right dates because:

    If we chose 6/1 as the Start Date which is Wednesday then it goes from 6/1 to 6/7 etc. whereas it should go from Wednesday till Saturday (6/1 to 6/4) and then next row should be from Sunday to Saturday again which would be from 6/5 to 6/11, 6/12 to 6/18 etc., and the last row should be 6/26 to 6/30 because the End Date is at 6/30.

    Thank you again for all the help !

    DECLARE @STARTDATE DATETIME,

    @ENDDATE DATETIME

    SET @STARTDATE ='06/01/2016'

    SET @ENDDATE = '06/30/2016'

    ;

    WITH CTE_DatesTable

    AS

    (

    SELECT CAST(@STARTDATE as datetime) AS [date]

    UNION ALL

    SELECT DATEADD(WEEK, 1, [date])

    FROM CTE_DatesTable

    WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE

    )

    SELECT

    [date] as StartDate,

    DATEADD(dd,6,[date]) as EndDate,

    row_number() over(ORDER BY [date]) AS [Week]

    FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

    STARTDATE ENDDATE WEEK

    2016-06-01 00:00:00.000 2016-06-07 00:00:00.000 1

    2016-06-08 00:00:00.000 2016-06-14 00:00:00.000 2

    2016-06-15 00:00:00.000 2016-06-21 00:00:00.000 3

    2016-06-22 00:00:00.000 2016-06-28 00:00:00.000 4

    2016-06-29 00:00:00.000 2016-07-05 00:00:00.000 5

    STARTDATE ENDDATE WEEK

    2016-06-01 00:00:00.000 2016-06-04 00:00:00.000 1

    2016-06-05 00:00:00.000 2016-06-11 00:00:00.000 2

    2016-06-12 00:00:00.000 2016-06-18 00:00:00.000 3

    2016-06-19 00:00:00.000 2016-06-25 00:00:00.000 4

    2016-06-26 00:00:00.000 2016-06-30 00:00:00.000 5

  • DiabloSlayer (6/13/2016)


    Thanks to both twin.devil and Lowell for the quick solution, that's exactly the output I was looking for and presented to the client however ..

    The client now added a twist to the requirements this morning by saying that the Start Date should be from beginning of the month till the end of the month, for instance for June it would be from 6/1 thru 6/30.

    In this case the SQL query that I have will not work to get the right dates because:

    If we chose 6/1 as the Start Date which is Wednesday then it goes from 6/1 to 6/7 etc. whereas it should go from Wednesday till Saturday (6/1 to 6/4) and then next row should be from Sunday to Saturday again which would be from 6/5 to 6/11, 6/12 to 6/18 etc., and the last row should be 6/26 to 6/30 because the End Date is at 6/30.

    Thank you again for all the help !

    DECLARE @STARTDATE DATETIME,

    @ENDDATE DATETIME

    SET @STARTDATE ='06/01/2016'

    SET @ENDDATE = '06/30/2016'

    ;

    WITH CTE_DatesTable

    AS

    (

    SELECT CAST(@STARTDATE as datetime) AS [date]

    UNION ALL

    SELECT DATEADD(WEEK, 1, [date])

    FROM CTE_DatesTable

    WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE

    )

    SELECT

    [date] as StartDate,

    DATEADD(dd,6,[date]) as EndDate,

    row_number() over(ORDER BY [date]) AS [Week]

    FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

    STARTDATE ENDDATE WEEK

    2016-06-01 00:00:00.000 2016-06-07 00:00:00.000 1

    2016-06-08 00:00:00.000 2016-06-14 00:00:00.000 2

    2016-06-15 00:00:00.000 2016-06-21 00:00:00.000 3

    2016-06-22 00:00:00.000 2016-06-28 00:00:00.000 4

    2016-06-29 00:00:00.000 2016-07-05 00:00:00.000 5

    STARTDATE ENDDATE WEEK

    2016-06-01 00:00:00.000 2016-06-04 00:00:00.000 1

    2016-06-05 00:00:00.000 2016-06-11 00:00:00.000 2

    2016-06-12 00:00:00.000 2016-06-18 00:00:00.000 3

    2016-06-19 00:00:00.000 2016-06-25 00:00:00.000 4

    2016-06-26 00:00:00.000 2016-06-30 00:00:00.000 5

    With changes to my code:

    D E C L A R E -- spaced to permit posting from current location

    @STARTDATE DATETIME,

    @ENDDATE DATETIME;

    SET @STARTDATE ='06/01/2016';

    SET @ENDDATE = '06/30/2016';

    WITH CTE_DatesTable

    AS

    (

    SELECT CAST(@STARTDATE as datetime) AS [date]

    UNION ALL

    SELECT DATEADD(WEEK, 1, [date])

    FROM CTE_DatesTable

    WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE

    )

    SELECT [date] FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

    with

    e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

    e2(n) as (select 1 from e1 a cross join e1 b),

    e4(n) as (select 1 from e2 a cross join e2 b),

    eTally(n) as (select top(datediff(week,@STARTDATE,@ENDDATE) + 1) rn = row_number() over (order by (select null)) - 1 from e4)

    select

    --dateadd(week,n,@STARTDATE) STARTDATE,

    --dateadd(day,-1,dateadd(week,n + 1,@STARTDATE)) ENDDATE,

    case when dateadd(day,-1,dateadd(week,datediff(week,0,dateadd(week,n,@STARTDATE)),0)) >= @STARTDATE then dateadd(day,-1,dateadd(week,datediff(week,0,dateadd(week,n,@STARTDATE)),0)) else @STARTDATE end StartDate,

    case when dateadd(day,-2,dateadd(week,datediff(week,0,dateadd(week,n + 1,@STARTDATE)),0)) <= @ENDDATE then dateadd(day,-2,dateadd(week,datediff(week,0,dateadd(week,n + 1,@STARTDATE)),0)) else @ENDDATE end EndDate,

    n + 1 [WEEK]

    from eTally;

  • DiabloSlayer (6/13/2016)

    If we chose 6/1 as the Start Date which is Wednesday then it goes from 6/1 to 6/7 etc. whereas it should go from Wednesday till Saturday (6/1 to 6/4) and then next row should be from Sunday to Saturday again which would be from 6/5 to 6/11, 6/12 to 6/18 etc., and the last row should be 6/26 to 6/30 because the End Date is at 6/30.

    How should this logic work for StartDate = '01/01/2017'?

    Hint - it's Sunday. And it's 1st of the month.

    _____________
    Code for TallyGenerator

  • Hi Sergiy,

    In this case, it would be like the following:

    STARTDATE ENDDATE WEEK

    2017-01-01 00:00:00.000 2017-01-07 00:00:00.000 1

    2017-01-08 00:00:00.000 2017-01-14 00:00:00.000 2

    2017-01-15 00:00:00.000 2017-01-21 00:00:00.000 3

    2017-01-22 00:00:00.000 2017-01-28 00:00:00.000 4

    2017-01-29 00:00:00.000 2017-01-31 00:00:00.000 5

  • DiabloSlayer (6/13/2016)


    Hi Sergiy,

    In this case, it would be like the following:

    STARTDATE ENDDATE WEEK

    2017-01-01 00:00:00.000 2017-01-07 00:00:00.000 1

    2017-01-08 00:00:00.000 2017-01-14 00:00:00.000 2

    2017-01-15 00:00:00.000 2017-01-21 00:00:00.000 3

    2017-01-22 00:00:00.000 2017-01-28 00:00:00.000 4

    2017-01-29 00:00:00.000 2017-01-31 00:00:00.000 5

    Ah, I see.

    Sorry, misread the requirements at the first time.

    I believe everything will be much simpler if you use static Calendar table - populated with all days you will possibly deal with.

    Then the query which you actually need for reports will look like this:

    DECLARE @StartDate DATETIME

    SET @StartDate = '20160601'

    SELECT bc.Date, DATENAME(dw, bc.Date),

    DATEDIFF(dd, 6, bc.Date)/7 - DATEDIFF(dd, 6, @StartDate)/7 +1 WeekNo

    FROM dbo.BusinessCalendar bc

    WHERE bc.Date >= @StartDate

    AND bc.Date < DATEADD(mm, 1, @StartDate)

    If you want to see STARTDATE and ENDDATE for every week just run the aggregation on top of this query:

    SELECT @StartDate [@StartDate],

    WeekNo, MIN(Date) FromDate, MAX(Date) ToDate

    FROM (

    SELECT bc.Date, DATENAME(dw, bc.Date) WeekDay,

    DATEDIFF(dd, 6, bc.Date)/7 - DATEDIFF(dd, 6, @StartDate)/7 +1 WeekNo

    FROM dbo.BusinessCalendar bc

    WHERE bc.Date >= @StartDate

    AND bc.Date < DATEADD(mm, 1, @StartDate)

    ) DT

    GROUP BY WeekNo

    _____________
    Code for TallyGenerator

  • You can even have a query for all the dates ever:

    SELECT DATENAME(MONTH, MonthStart) + ' ' + DATENAME(YY, DT.MonthStart) ReportMonth,

    WeekNo, MIN(Date) FromDate, MAX(Date) ToDate

    FROM (

    SELECT DATEADD(mm, DATEDIFF(mm, 0, bc.Date), 0) MonthStart,

    bc.Date,

    DATENAME(dw, bc.Date) WeekDay,

    DATEDIFF(dd, 6, bc.Date)/7 - DATEDIFF(dd, 6, DATEADD(mm, DATEDIFF(mm, 0, bc.Date), 0))/7 +1 WeekNo

    FROM dbo.BusinessCalendar bc

    ) DT

    GROUP BY MonthStart, WeekNo

    ORDER BY MonthStart, WeekNo

    Not @StartDate required.

    It will show correct Month and WeekInMonth for all the dates included in your report.

    Another advantage of static Calendar table - it may have all the holidays accounted, so you don't need to figure out the Easters schedule every time you run a report.

    It's especially valuable when you have different holidays for different locations.

    _____________
    Code for TallyGenerator

  • Hi Sergiy,

    Do I need to create the BusinessCalendar table first?

    I'm getting an unknown invalid object name error.

    Thank you.

  • Yes, of course.

    It's pretty much Numbers table, but instead of numbers it contains dates:

    Select DATEADD (dd, Number, 0)

    From Tally

    Where Number < 256*256

    It will cover full range of SMALLDATETIME dates

    _____________
    Code for TallyGenerator

  • Hi Lynn Pettis,

    That's exactly the results I need so THANK YOU very much !!

    But... I don't know two things, so if you could please help me 🙂

    1. How do I remove the first result from your query as shown below?

    I mean, when I run the following query I get 'dates' result and the StartDate, EndDate and Week result. I just need the second three column result so I can insert it into my staging table.

    2. Where can I learn more about what exactly your query is doing?

    Thank you very much..

    DECLARE

    @STARTDATE DATETIME,

    @ENDDATE DATETIME;

    SET @STARTDATE ='06/01/2016';

    SET @ENDDATE = '06/30/2016';

    WITH CTE_DatesTable

    AS

    (

    SELECT CAST(@STARTDATE as datetime) AS [date]

    UNION ALL

    SELECT DATEADD(WEEK, 1, [date])

    FROM CTE_DatesTable

    WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE

    )

    SELECT [date] FROM CTE_DatesTable

    OPTION (MAXRECURSION 0);

    with

    e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

    e2(n) as (select 1 from e1 a cross join e1 b),

    e4(n) as (select 1 from e2 a cross join e2 b),

    eTally(n) as (select top(datediff(week,@STARTDATE,@ENDDATE) + 1) rn = row_number() over (order by (select null)) - 1 from e4)

    select

    --dateadd(week,n,@STARTDATE) STARTDATE,

    --dateadd(day,-1,dateadd(week,n + 1,@STARTDATE)) ENDDATE,

    case when dateadd(day,-1,dateadd(week,datediff(week,0,dateadd(week,n,@STARTDATE)),0)) >= @STARTDATE then dateadd(day,-1,dateadd(week,datediff(week,0,dateadd(week,n,@STARTDATE)),0)) else @STARTDATE end StartDate,

    case when dateadd(day,-2,dateadd(week,datediff(week,0,dateadd(week,n + 1,@STARTDATE)),0)) <= @ENDDATE then dateadd(day,-2,dateadd(week,datediff(week,0,dateadd(week,n + 1,@STARTDATE)),0)) else @ENDDATE end EndDate,

    n + 1 [WEEK]

    from eTally;

  • Hi Sergiy,

    I'm sorry I'm lost 😉

    Can you kindly add the initial piece (where we create the Calendar table) into your query?

    Sorry and thanks again for your help.

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply