How can I fetch a list of available hours in SQL?-CodePudding

Here's the use case: a client can register a schedule in a room from one date to another. I'm trying to implement a calendar in which the client can click on a day and get the available hours to schedule that room. This is what I've tried so far, it only works if the day has a single schedule associated with it:

SELECT {Hours}.*
FROM {Hours}
WHERE {Hours}.[Id] BETWEEN (
    SELECT
        CASE
            WHEN @DateInput = {Schedule}.[StartDate] THEN 0
            WHEN @DateInput > {Schedule}.[StartDate] AND @DateInput < {Schedule}.[EndDate] THEN 23
            WHEN @DateInput = {Schedule}.[EndDate] THEN {Schedule}.[EndHour]
        END
    FROM {Schedule}
    WHERE {Schedule}.[RoomId] = @RoomInput
    ) AND (
    SELECT
        CASE
            WHEN @DateInput = {Schedule}.[StartDate] THEN {Schedule}.[StartHour] - 1
            WHEN @DateInput > {Schedule}.[StartDate] AND @DateInput < {Schedule}.[EndDate] THEN 0
            WHEN @DateInput = {Schedule}.[EndDate] THEN 23
        END
    FROM {Schedule}
    WHERE {Schedule}.[RoomId] = @RoomInput
    )

CodePudding user response：

I am not familiar with the curly bracket notation like {Hours}, but I will assume that is an outsystems thing and run with it.

Instead of selecting hours between two values, I believe you need to structure your query to select hours which are not a part of any existing schedule entry. This can be done with a NOT EXISTS() condition. Something like:

SELECT H.*
FROM {Hours} H
WHERE NOT EXISTS (
    SELECT *
    FROM {Schedule} S
    WHERE S.RoomId = @RoomInput
    AND (S.StartDate < @DateInput
         OR (S.StartDate = @DateInput AND S.StartHour <= H.Id))
    AND (S.EndDate > @DateInput
         OR (S.EndDate = @DateInput AND S.EndHour >= H.Id))
)

Comparisons such as this are usually easier to write if dates and times are combined.

    WHERE S.RoomId = @RoomInput
    AND DATEADD(hour, H.Id, @DateInput)
        BETWEEN DATEADD(hour, S.StartHour, S.StartDate)
            AND DATEADD(hour, S.EndHour, S.EndDate)

You may need to tweak the end comparison depending on whether EndDate is inclusive or exclusive. (Does a schedule entry with EndHour = 10 end at 10:00 or 11:00?)

For working demos, see

this db<>fiddle using inclusive dates.
this db<>fiddle using exclusive end dates (where a 1 hour meeting starting at 9am has EndHour = 10).
this db<>fiddle using combined date/times and exclusive end date/time.