SQL Query to find used time slots for appointment not working [closed]

I am trying to write an SQL Query that looks for any time slots in the table.

My table has the following appointments slots reserved for a specific day – say 01/11/2020.

StartTime   EndTime
    09:55   10:55
    15:00   16:00
    14:00   15:00
    13:00   13:30
    12:15   16:00
    12:00   16:00

My SQL Query is as follows:

SELECT 
        *
    FROM 
        dbo.Appointment
    WHERE
                AppointmentDate = '01/11/2020'
                AND IsActive = 'True'
                AND
                (CAST(StartTime AS TIME) BETWEEN
                CAST('10:00' AS TIME) AND CAST('11:55' AS TIME)
                AND CAST(EndTime AS TIME) BETWEEN
                CAST('10:00' AS TIME) AND CAST('11:55' AS TIME))

The problem is that the query is returning zero rows. Infact, it should return me the row 09:55 and 10:55 as this record falls in the time range that I have provided: 10:00 - 11:55

Can anyone help me to figure out the issue please?

Answer

A slot does not overlap with another if it ends before the other starts, or it starts after the other ends.

where '11:55' < StartTime or EndTime < '10:00'

To find overlapping slots, invert the rule. It ends after the other starts and it starts before the other ends.

where StartTime < '11:55' and '10:00' < EndTime

There’s no need to cast the values, MySQL will do it for you, assuming StartTime and EndTime are time columns.

Try it.

Advertisement

Answer