TSQL - SQL 2008 - Across a date range (Start Date to End Date) is it possible to break this range into seperate rows by (and exluding) weekends?

Question

I apologize for the poorly worded title, I have been given a task beyond my limited skillset and was hoping someone might help.

We have employees who have 24/7 schedules for booking work and field assignments (over weekends as well) but this does not apply to vacation. Because of this I have been tasked to break a single date range up across weekends (and exclude them)

For Example:

Start Date: 30/04/2015 End Date: 13/05/2015

30/04/2015, 01/05/2015
04/05/2015, 05/05/2015, 06/05/2015, 07/05/2015, 08/05/2015,
11/05/2015, 12/05/2015, 13/05/2015,

Note: The weekends have been excluded and the date range has been split into three across the three weeks.

Preferably: Include the start and end points for each range like so

30/04/2015 - 01/05/2015 --(the same as it is the start and end dates)
04/05/2015 - 08/05/2015
11/05/2015 - 13/05/2015

I have no idea if this is even possible due to my very limited knowledge and hope I have explained enough so that some kind soul could potentially see if such a thing is even possible.

The database application we use is TSQL on SQL 2008.

Many thanks.

Thank you for the edit Stephan – Hiigara May 13 '15 at 15:02 — Hiigara, May 13 '15 at 15:02

Stephan · Answer 1 · 2015-05-13T14:56:19.763

Thanks for the fun problem. Note: I use the standard date format, but the concept is the same.

DECLARE @StartDate  DATE = '20150430',  --April 30, 2015
        @EndDate    DATE = '20150513';  --May 13,2015

WITH CTE_Dates
AS
(
    SELECT @StartDate dates
    UNION ALL
    SELECT DATEADD(DAY,1,dates)
    FROM CTE_Dates
    WHERE dates < @EndDate
),
CTE_weeks
AS
(
    SELECT  dates,
            DATEPART(WEEK,dates) WeekID
    FROM CTE_Dates
    WHERE DATENAME(WEEKDAY,dates) NOT IN ('Saturday','Sunday') --doesn't include weekends
)

SELECT  WeekID,
        MIN(dates) StartDate,
        MAX(dates) EndDate,
        STUFF(list_dates,1,1,'') list
FROM CTE_weeks A
CROSS APPLY (
                SELECT ',' + CAST(dates AS VARCHAR(100))
                FROM CTE_weeks B
                WHERE A.WeekID = B.WeekID
                ORDER BY dates
                FOR XML PATH('')
            ) CA(list_dates)
GROUP BY WeekID,STUFF(list_dates,1,1,'')

Results:

WeekID      StartDate  EndDate    list
----------- ---------- ---------- ------------------------------------------------------------
18          2015-04-30 2015-05-01 2015-04-30,2015-05-01
19          2015-05-04 2015-05-08 2015-05-04,2015-05-05,2015-05-06,2015-05-07,2015-05-08
20          2015-05-11 2015-05-13 2015-05-11,2015-05-12,2015-05-13

I think you're assuming a particular `DATEFIRST` setting here - there's no guarantee what `weekday` values are weekends. E.g. `SET DATEFIRST 4; SELECT DATEPART(weekday,'20150509')` returns 3. — Damien_The_Unbeliever, May 13 '15 at 14:50
That just shifts the problem to what language settings are in force for the user. — Damien_The_Unbeliever, May 13 '15 at 16:29

score -1 · Answer 2 · answered May 13 '15 at 14:46

This seems to work. It assumes you give it a start date that's a weekday:

declare @StartDate datetime = '20150430'
declare @EndDate datetime = '20150513'

; With Ord as (
    select @StartDate as StartAt,@StartDate as EndAt
    union all
    select StartAt,DATEADD(day,1,EndAt)
    from Ord where DATEPART(weekday,EndAt) != DATEPART(weekday,'20150710') --Known Friday
    and EndAt < @EndDate
    union all
    select DATEADD(day,3,EndAt),DATEADD(day,3,EndAt)
    from Ord where DATEPART(weekday,EndAt) = DATEPART(weekday,'20150710') --Still known Friday
    and DATEADD(day,3,EndAt) <= @EndDate
)
select StartAt,MAX(EndAt) as EndAt
from Ord
group by StartAt

Result:

StartAt                 EndAt
----------------------- -----------------------
2015-04-30 00:00:00.000 2015-05-01 00:00:00.000
2015-05-04 00:00:00.000 2015-05-08 00:00:00.000
2015-05-11 00:00:00.000 2015-05-13 00:00:00.000

I do my comparisons about DATEPART using a "known good" (i.e. I just randomly selected one from a calendar) Friday so that this code works for any DATEFIRST setting.

It certainly wasn't me, thank you very much for your time and effort, this has been a massive help in my task. +1 — Hiigara, May 13 '15 at 19:01

TSQL - SQL 2008 - Across a date range (Start Date to End Date) is it possible to break this range into seperate rows by (and exluding) weekends?

2 Answers2