Skip to content Skip to sidebar Skip to footer
Home / Sql Server / Stored Procedures

Stored Procedure To Find Free Slots With Different Work Times Per Day Of The Week

Post a Comment
I am using the awesome stored procedure created by Aaron Bertrand here as a start since it almost does what I need. Sadly my SQL skills are not as good as his so I need some help h

Solution 1:

Ok, this answer builds on the last one, we're going to add the lunch intervals and the events and going to look at a date range for events.

First lets make some test data:

DECLARE @event TABLE
(
    [event_id] [int] IDENTITY(1,1),
    [event_start] [datetime],
    [event_end] [datetime],
    [UserId] [int]
)

INSERT INTO @event
( event_start, event_end, UserId )
VALUES
('12/01/2014 09:00:00', '12/01/2014 09:30:00', 1),
('12/01/2014 09:30:00', '12/01/2014 10:30:00', 1),
('12/01/2014 11:00:00', '12/01/2014 12:00:00', 1),
('12/01/2014 13:00:00', '12/01/2014 16:30:00', 1);

DECLARE @Users TABLE
(
    [UserID] [int],
    [Username] [varchar](32) NULL,
    [Sunday] [bit] NULL,
    [Monday] [bit] NULL,
    [Tuesday] [bit] NULL,
    [Wednesday] [bit] NULL,
    [Thursday] [bit] NULL,
    [Friday] [bit] NULL,
    [Saturday] [bit] NULL,
    [WorkStartSunday] [time](7) NULL,
    [WorkEndSunday] [time](7) NULL,
    [IntervalStartSunday] [time](7) NULL,
    [IntervalEndSunday] [time](7) NULL,
    [WorkStartMonday] [time](7) NULL,
    [WorkEndMonday] [time](7) NULL,
    [IntervalStartMonday] [time](7) NULL,
    [IntervalEndMonday] [time](7) NULL,
    [WorkStartTuesday] [time](7) NULL,
    [WorkEndTuesday] [time](7) NULL,
    [IntervalStartTuesday] [time](7) NULL,
    [IntervalEndTuesday] [time](7) NULL,
    [WorkStartWednesday] [time](7) NULL,
    [WorkEndWednesday] [time](7) NULL,
    [IntervalStartWednesday] [time](7) NULL,
    [IntervalEndWednesday] [time](7) NULL,
    [WorkStartThursday] [time](7) NULL,
    [WorkEndThursday] [time](7) NULL,
    [IntervalStartThursday] [time](7) NULL,
    [IntervalEndThursday] [time](7) NULL,
    [WorkStartFriday] [time](7) NULL,
    [WorkEndFriday] [time](7) NULL,
    [IntervalStartFriday] [time](7) NULL,
    [IntervalEndFriday] [time](7) NULL,
    [WorkStartSaturday] [time](7) NULL,
    [WorkEndSaturday] [time](7) NULL,
    [IntervalStartSaturday] [time](7) NULL,
    [IntervalEndSaturday] [time](7) NULL
);

INSERT INTO @Users 
([UserID], [Username], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [WorkStartSunday], [WorkEndSunday], [IntervalStartSunday], [IntervalEndSunday], [WorkStartMonday], [WorkEndMonday], [IntervalStartMonday], [IntervalEndMonday], [WorkStartTuesday], [WorkEndTuesday], [IntervalStartTuesday], [IntervalEndTuesday], [WorkStartWednesday], [WorkEndWednesday], [IntervalStartWednesday], [IntervalEndWednesday], [WorkStartThursday], [WorkEndThursday], [IntervalStartThursday], [IntervalEndThursday], [WorkStartFriday], [WorkEndFriday], [IntervalStartFriday], [IntervalEndFriday], [WorkStartSaturday], [WorkEndSaturday], [IntervalStartSaturday], [IntervalEndSaturday]) 
VALUES 
(1, 'User A', 0, 1, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL, CAST('08:00:00' AS Time), CAST('18:00:00' AS Time), CAST('12:00:00' AS Time), CAST('13:00:00' AS Time), CAST('10:00:00' AS Time), CAST('15:00:00' AS Time), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

Once again, I am going to create a numbers table, now lets set our data range, and I am also going to generate time slots based on a 30 minute duration.

DECLARE@yearstart DATETIME ='01/01/2014'DECLARE@duration_minutes INT=30DECLARE@number_of_numbers INT=100000;
;WITH
    a AS (SELECT1AS i UNIONALLSELECT1),
    b AS (SELECT1AS i FROM a AS x, a AS y),
    c AS (SELECT1AS i FROM b AS x, b AS y),
    d AS (SELECT1AS i FROM c AS x, c AS y),
    e AS (SELECT1AS i FROM d AS x, d AS y),
    f AS (SELECT1AS i FROM e AS x, e AS y),
    numbers AS 
(
    SELECT TOP(@number_of_numbers)
    (ROW_NUMBER() OVER (ORDERBY (SELECTNULL))) -1AS number
    FROM f
)
, mydays AS
(
    SELECT DATEADD(DAY, number, @yearstart) CheckDate
    FROM numbers
)
, myduration AS
(
    SELECT DATEADD(MINUTE, number *@duration_minutes, @yearstart) CheckMinute
    FROM numbers
)
, workdays AS
(
    SELECT 
        m.CheckDate +CAST(u.WorkStartSunday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndSunday as datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =1AND u.Sunday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartMonday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndMonday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =2AND u.Monday =1)
        UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartTuesday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndTuesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =3AND u.Tuesday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartWednesday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndWednesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =4AND u.Wednesday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartThursday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndThursday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =5AND u.Thursday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartFriday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndFriday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =6AND u.Friday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartSaturday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndSaturday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =7AND u.Saturday =1)
),
intervals AS
(
    SELECT 
        m.CheckDate +CAST(u.IntervalStartSunday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndSunday as datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =1AND u.Sunday =1)
        AND u.IntervalStartSunday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartMonday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndMonday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =2AND u.Monday =1)
        AND u.IntervalStartMonday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartTuesday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndTuesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =3AND u.Tuesday =1)
        AND u.IntervalStartTuesday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartWednesday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndWednesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =4AND u.Wednesday =1)
        AND u.IntervalStartWednesday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartThursday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndThursday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =5AND u.Thursday =1)
        AND u.IntervalStartThursday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartFriday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndFriday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =6AND u.Friday =1)
        AND u.IntervalStartFriday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartSaturday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndSaturday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =7AND u.Saturday =1)
        AND u.IntervalStartSaturday ISNOTNULL
)
, timeslots AS
(
    SELECT m.CheckMinute AS StartSlot,
        LEAD(m.CheckMinute) OVER (ORDERBY m.CheckMinute) AS EndSlot
    FROM myduration m
)

Now I also created the intervals (lunch hour data) and our final output will be a list of time slots and whether it is open, event, or lunch for a date range: 

select 
    t.StartSlot,
    t.EndSlot,
    CASEWHEN i.WorkStart IS NULL AND e.event_start IS NULL THEN'Open' WHEN i.WorkStart ISNOT NULL THEN'Lunch'ELSE'Event'ENDAS IType
from timeslots t
INNER JOIN workdays w
    ON t.StartSlot >= w.WorkStart 
    AND t.StartSlot < w.WorkEnd
LEFT JOIN intervals i
    ON t.StartSlot >= i.WorkStart
    AND t.StartSlot < i.WorkEnd
LEFT JOIN @event e
    ON t.StartSlot >= e.event_start
    AND t.StartSlot < e.event_end
WHERE w.WorkStart >= '12/01/2014'AND w.WorkEnd < '12/04/2014'ORDERBY t.StartSlot

Here is the output:

StartSlotEndSlotIType2014-12-01 08:00:00.000 2014-12-01 08:30:00.000 Open2014-12-01 08:30:00.000 2014-12-01 09:00:00.000 Open2014-12-01 09:00:00.000 2014-12-01 09:30:00.000 Event2014-12-01 09:30:00.000 2014-12-01 10:00:00.000 Event2014-12-01 10:00:00.000 2014-12-01 10:30:00.000 Event2014-12-01 10:30:00.000 2014-12-01 11:00:00.000 Open2014-12-01 11:00:00.000 2014-12-01 11:30:00.000 Event2014-12-01 11:30:00.000 2014-12-01 12:00:00.000 Event2014-12-01 12:00:00.000 2014-12-01 12:30:00.000 Lunch2014-12-01 12:30:00.000 2014-12-01 13:00:00.000 Lunch2014-12-01 13:00:00.000 2014-12-01 13:30:00.000 Event2014-12-01 13:30:00.000 2014-12-01 14:00:00.000 Event2014-12-01 14:00:00.000 2014-12-01 14:30:00.000 Event2014-12-01 14:30:00.000 2014-12-01 15:00:00.000 Event2014-12-01 15:00:00.000 2014-12-01 15:30:00.000 Event2014-12-01 15:30:00.000 2014-12-01 16:00:00.000 Event2014-12-01 16:00:00.000 2014-12-01 16:30:00.000 Event2014-12-01 16:30:00.000 2014-12-01 17:00:00.000 Open2014-12-01 17:00:00.000 2014-12-01 17:30:00.000 Open2014-12-01 17:30:00.000 2014-12-01 18:00:00.000 Open2014-12-02 10:00:00.000 2014-12-02 10:30:00.000 Open2014-12-02 10:30:00.000 2014-12-02 11:00:00.000 Open2014-12-02 11:00:00.000 2014-12-02 11:30:00.000 Open2014-12-02 11:30:00.000 2014-12-02 12:00:00.000 Open2014-12-02 12:00:00.000 2014-12-02 12:30:00.000 Open2014-12-02 12:30:00.000 2014-12-02 13:00:00.000 Open2014-12-02 13:00:00.000 2014-12-02 13:30:00.000 Open2014-12-02 13:30:00.000 2014-12-02 14:00:00.000 Open2014-12-02 14:00:00.000 2014-12-02 14:30:00.000 Open2014-12-02 14:30:00.000 2014-12-02 15:00:00.000 Open

Solution 2:

Ok lets make the test data:

DECLARE @Users TABLE
(
    [UserID] [int],
    [Username] [varchar](32) NULL,
    [Sunday] [bit] NULL,
    [Monday] [bit] NULL,
    [Tuesday] [bit] NULL,
    [Wednesday] [bit] NULL,
    [Thursday] [bit] NULL,
    [Friday] [bit] NULL,
    [Saturday] [bit] NULL,
    [WorkStartSunday] [time](7) NULL,
    [WorkEndSunday] [time](7) NULL,
    [IntervalStartSunday] [time](7) NULL,
    [IntervalEndSunday] [time](7) NULL,
    [WorkStartMonday] [time](7) NULL,
    [WorkEndMonday] [time](7) NULL,
    [IntervalStartMonday] [time](7) NULL,
    [IntervalEndMonday] [time](7) NULL,
    [WorkStartTuesday] [time](7) NULL,
    [WorkEndTuesday] [time](7) NULL,
    [IntervalStartTuesday] [time](7) NULL,
    [IntervalEndTuesday] [time](7) NULL,
    [WorkStartWednesday] [time](7) NULL,
    [WorkEndWednesday] [time](7) NULL,
    [IntervalStartWednesday] [time](7) NULL,
    [IntervalEndWednesday] [time](7) NULL,
    [WorkStartThursday] [time](7) NULL,
    [WorkEndThursday] [time](7) NULL,
    [IntervalStartThursday] [time](7) NULL,
    [IntervalEndThursday] [time](7) NULL,
    [WorkStartFriday] [time](7) NULL,
    [WorkEndFriday] [time](7) NULL,
    [IntervalStartFriday] [time](7) NULL,
    [IntervalEndFriday] [time](7) NULL,
    [WorkStartSaturday] [time](7) NULL,
    [WorkEndSaturday] [time](7) NULL,
    [IntervalStartSaturday] [time](7) NULL,
    [IntervalEndSaturday] [time](7) NULL
);

INSERT INTO @Users 
([UserID], [Username], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [WorkStartSunday], [WorkEndSunday], [IntervalStartSunday], [IntervalEndSunday], [WorkStartMonday], [WorkEndMonday], [IntervalStartMonday], [IntervalEndMonday], [WorkStartTuesday], [WorkEndTuesday], [IntervalStartTuesday], [IntervalEndTuesday], [WorkStartWednesday], [WorkEndWednesday], [IntervalStartWednesday], [IntervalEndWednesday], [WorkStartThursday], [WorkEndThursday], [IntervalStartThursday], [IntervalEndThursday], [WorkStartFriday], [WorkEndFriday], [IntervalStartFriday], [IntervalEndFriday], [WorkStartSaturday], [WorkEndSaturday], [IntervalStartSaturday], [IntervalEndSaturday]) 
VALUES 
(1, 'User A', 0, 1, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL, CAST('08:00:00' AS Time), CAST('18:00:00' AS Time), CAST('12:00:00' AS Time), CAST('13:00:00' AS Time), CAST('10:00:00' AS Time), CAST('15:00:00' AS Time), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

Now that we have the user record created, lets make a numbers table and set the beginning of the date range we are creating:

DECLARE@yearstart DATETIME ='01/01/2014'DECLARE@number_of_numbers INT=100000;
;WITH
    a AS (SELECT1AS i UNIONALLSELECT1),
    b AS (SELECT1AS i FROM a AS x, a AS y),
    c AS (SELECT1AS i FROM b AS x, b AS y),
    d AS (SELECT1AS i FROM c AS x, c AS y),
    e AS (SELECT1AS i FROM d AS x, d AS y),
    f AS (SELECT1AS i FROM e AS x, e AS y),
    numbers AS 
(
    SELECT TOP(@number_of_numbers)
    ROW_NUMBER() OVER (ORDERBY (SELECTNULL)) AS number
    FROM f
)

Using our numbers table, lets make some days to check against:

, mydaysAS
(
    SELECT DATEADD(DAY, number, @yearstart) CheckDate
    FROM numbers
)

Now using the user table, we are going to make a all of the working days of the week for the user, based on whether the flag is set, and the starting date / time and ending date / time

, workdaysAS
(
    SELECT 
        m.CheckDate + CAST(u.WorkStartSunday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndSunday as datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 1AND u.Sunday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartMonday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndMonday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 2AND u.Monday = 1)
        UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartTuesday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndTuesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 3AND u.Tuesday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartWednesday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndWednesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 4AND u.Wednesday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartThursday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndThursday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 5AND u.Thursday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartFriday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndFriday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 6AND u.Friday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartSaturday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndSaturday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 7AND u.Saturday = 1)
)

Now lets look at the data we generated:

SELECT w.WorkStart, w.WorkEnd, DATENAME(WEEKDAY, w.WorkStart) DayOfWeek, w.UserID, w.Username
FROM workdays w
where w.WorkStart >='12/01/2014'AND w.WorkEnd <'01/01/2015'

Here is the output:

WorkStartWorkEndDayOfWeekUserIDUsername2014-12-01 08:00:00.000 2014-12-01 18:00:00.000 Monday1UserA2014-12-02 10:00:00.000 2014-12-02 15:00:00.000 Tuesday1UserA2014-12-08 08:00:00.000 2014-12-08 18:00:00.000 Monday1UserA2014-12-09 10:00:00.000 2014-12-09 15:00:00.000 Tuesday1UserA2014-12-15 08:00:00.000 2014-12-15 18:00:00.000 Monday1UserA2014-12-16 10:00:00.000 2014-12-16 15:00:00.000 Tuesday1UserA2014-12-22 08:00:00.000 2014-12-22 18:00:00.000 Monday1UserA2014-12-23 10:00:00.000 2014-12-23 15:00:00.000 Tuesday1UserA2014-12-29 08:00:00.000 2014-12-29 18:00:00.000 Monday1UserA2014-12-30 10:00:00.000 2014-12-30 15:00:00.000 Tuesday1UserA

You can do the same thing for the intervals (I guess the interval is the lunch break?), and then join against them to find the work times available, and then join against the events to see which work times you can create an event against.

Post a Comment for "Stored Procedure To Find Free Slots With Different Work Times Per Day Of The Week"