SQL query to generate one hour time slots between the start TIme and end Time

Question

I need to generate result for time slots between dates and for time

select scheduleId,startDate,endDate,startTime,endTime,weekdayMask,
       (CASE WHEN [weekdayMask] | 0x01 = [weekdayMask] THEN 1 ELSE 0 END) AS MONDAY, 
       (CASE WHEN [weekdayMask] | 0x02 = [weekdayMask] THEN 1 ELSE 0 END) AS TUESDAY, 
       (CASE WHEN [weekdayMask] | 0x04 = [weekdayMask] THEN 1 ELSE 0 END) AS WEDNESDAY, 
       (CASE WHEN [weekdayMask] | 0x08 = [weekdayMask] THEN 1 ELSE 0 END) AS THURSDAY, 
       (CASE WHEN [weekdayMask] | 0x10 = [weekdayMask] THEN 1 ELSE 0 END) AS FRIDAY, 
       (CASE WHEN [weekdayMask] | 0x20 = [weekdayMask] THEN 1 ELSE 0 END) AS SATURDAY, 
       (CASE WHEN [weekdayMask] | 0x40 = [weekdayMask] THEN 1 ELSE 0 END) AS SUNDAY
 from otp.tutor_schedule 

RESULT from the above query

scheduleId  startDate  endDate    startTime        endTime          weekdayMask MONDAY      TUESDAY     WEDNESDAY   THURSDAY    FRIDAY      SATURDAY    SUNDAY
----------- ---------- ---------- ---------------- ---------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
11          2017-01-01 2017-09-30 10:00:00.0000000 14:00:00.0000000 21          1           0           1           0           1           0           0

Now I need to generate the one hour time slots for the weekdays between the StartDate and endDate fields.

SQL fiddle link http://sqlfiddle.com/#!18/49f1b/1

Can any one please help me out

Answer 1

Perhaps a recursive CTE will get you started down the right path?

;WITH cte AS(
SELECT CAST('2018-01-01' AS DATETIME) AS StartDate
UNION ALL
SELECT DATEADD(HH, 1, StartDate)
FROM cte
WHERE StartDate < '2018-09-30'
)

SELECT *
FROM cte
OPTION (MAXRECURSION 7000) 

Answer 2

Please check following CTE query

I used a calendar table as you can see in first CTE expression, dbo.DateTable

For time, I mean hours I used basic method; reading from spt_values But you can also create a time table as well

I hope it helps

;with dt as (
    select
        convert(date, [date]) as [date],
        DATEPART(dw, [date]) as wd,
        case when DATEPART(dw, [date]) = 1 then 1 end as SUNDAY,
        case when DATEPART(dw, [date]) = 2 then 1 end as MONDAY,
        case when DATEPART(dw, [date]) = 3 then 1 end as TUESDAY,
        case when DATEPART(dw, [date]) = 4 then 1 end as WEDNESDAY,
        case when DATEPART(dw, [date]) = 5 then 1 end as THURSDAY,
        case when DATEPART(dw, [date]) = 6 then 1 end as FRIDAY,
        case when DATEPART(dw, [date]) = 7 then 1 end as SATURDAY
    from [dbo].[DateTable]('2017-01-01', '2017-12-31') as dt
), tt as (
    select convert(time, dateadd(hh,number-1,'00:00:00')) as [time]
    from master..spt_values
    where Type = 'P' and number between 1 and 25
), schedule as (
    select
        scheduleId,startDate,endDate,startTime,endTime,weekdayMask,
        (CASE WHEN [weekdayMask] | 0x01 = [weekdayMask] THEN 1 ELSE 0 END) AS MONDAY, 
        (CASE WHEN [weekdayMask] | 0x02 = [weekdayMask] THEN 1 ELSE 0 END) AS TUESDAY, 
        (CASE WHEN [weekdayMask] | 0x04 = [weekdayMask] THEN 1 ELSE 0 END) AS WEDNESDAY, 
        (CASE WHEN [weekdayMask] | 0x08 = [weekdayMask] THEN 1 ELSE 0 END) AS THURSDAY, 
        (CASE WHEN [weekdayMask] | 0x10 = [weekdayMask] THEN 1 ELSE 0 END) AS FRIDAY, 
        (CASE WHEN [weekdayMask] | 0x20 = [weekdayMask] THEN 1 ELSE 0 END) AS SATURDAY, 
        (CASE WHEN [weekdayMask] | 0x40 = [weekdayMask] THEN 1 ELSE 0 END) AS SUNDAY
    from tutor_schedule
), cte as (
    select
        dt.[date],
        scheduleId,startDate,endDate,startTime,endTime,
        ROW_NUMBER() over (partition by scheduleId, [date] order by [time]) as rn,
        tt.[time]
    from dt
    left join schedule as s
        on dt.[date] between s.startDate and s.endDate
    left join tt
        on tt.[time] >= s.startTime and tt.[time] < s.endTime
    where
        dt.MONDAY = s.MONDAY or
        dt.TUESDAY = s.TUESDAY or
        dt.WEDNESDAY = s.WEDNESDAY or
        dt.THURSDAY = s.THURSDAY or
        dt.FRIDAY = s.FRIDAY or
        dt.SATURDAY = s.SATURDAY or
        dt.SUNDAY = s.SUNDAY
)
select
scheduleId,
[date],
dateadd(hh, rn-1,startTime) as starttime,
dateadd(hh, rn,startTime) as endtime
from cte
where starttime <= [time]

I have tested with following sample data

create table tutor_schedule (
    scheduleId int identity(1,1),
    startDate date,
    endDate date,
    startTime time,
    endTime time,
    weekdayMask int
 )

 insert into tutor_schedule select '2017-01-01','2017-09-30','10:00:00','14:00:00',21
 insert into tutor_schedule select '2017-01-05','2017-01-12','13:00:00','15:00:00',12

Answer 3

You can use the following. Put your original query on the first CTE (I hard-coded some values to test).

Basically I use a recursive CTE to generate all hours between each start date and end date. Then filter the results depending on each weekday, for each schedule. The first statement is used to set the DATEPART WEEKDAY to a known value to compare later.

SET DATEFIRST 1 -- 1: Monday, 7: Sunday

;WITH YourQueryResults AS
(
    SELECT
        scheduleId = 1,
        startDate = CONVERT(DATE, '2017-01-01'),
        endDate = CONVERT(DATE, '2017-09-30'),
        startTime = CONVERT(TIME, '10:00:00.0000000'),
        endTime = CONVERT(TIME, '14:00:00.0000000'),
        MONDAY = 1,
        TUESDAY = 0,
        WEDNESDAY = 1,
        THURSDAY = 0,
        FRIDAY = 1,
        SATURDAY = 0,
        SUNDAY = 0
    UNION ALL
    SELECT
        scheduleId = 2,
        startDate = CONVERT(DATE, '2017-01-01'),
        endDate = CONVERT(DATE, '2017-09-30'),
        startTime = CONVERT(TIME, '17:00:00.0000000'),
        endTime = CONVERT(TIME, '20:00:00.0000000'),
        MONDAY = 1,
        TUESDAY = 0,
        WEDNESDAY = 0,
        THURSDAY = 1,
        FRIDAY = 0,
        SATURDAY = 0,
        SUNDAY = 0
),
RecursiveDatetimesByHour AS
(
    SELECT
        scheduleId = Y.scheduleId,
        DatetimeByHour = CONVERT(DATETIME, Y.startDate) + CONVERT(DATETIME, Y.startTime),
        WeekDay = DATEPART(WEEKDAY, CONVERT(DATETIME, Y.startDate) + CONVERT(DATETIME, Y.startTime))
    FROM
        YourQueryResults AS Y

    UNION ALL

    SELECT
        scheduleId = R.scheduleId,
        DatetimeByHour = DATEADD(HOUR, 1, R.DatetimeByHour),
        WeekDay = DATEPART(WEEKDAY, DATEADD(HOUR, 1, R.DatetimeByHour))
    FROM
        RecursiveDatetimesByHour AS R
    WHERE
        DATEADD(HOUR, 1, R.DatetimeByHour) < 
            (SELECT CONVERT(DATETIME, Y.endDate) + CONVERT(DATETIME, Y.endTime) FROM YourQueryResults AS Y
             WHERE Y.scheduleId = R.scheduleId)
)
SELECT
    R.scheduleId,
    R.DatetimeByHour,
    R.WeekDay
FROM
    RecursiveDatetimesByHour AS R
    INNER JOIN YourQueryResults AS Y ON R.scheduleId = Y.scheduleId
WHERE
    CONVERT(TIME, R.DatetimeByHour) BETWEEN Y.startTime AND Y.endTime AND
    (
        (Y.MONDAY = 1 AND R.WeekDay = 1) OR
        (Y.TUESDAY = 1 AND R.WeekDay = 2) OR
        (Y.WEDNESDAY = 1 AND R.WeekDay = 3) OR
        (Y.THURSDAY = 1 AND R.WeekDay = 4) OR
        (Y.FRIDAY = 1 AND R.WeekDay = 5) OR
        (Y.SATURDAY = 1 AND R.WeekDay = 6) OR
        (Y.SUNDAY = 1 AND R.WeekDay = 7)
    )
OPTION
    (MAXRECURSION 32000)

Some things can be improved (avoid doing so many casts for example), but it can give you a good idea of how to solve your problem.