SQLTeam.com | Weblogs | Forums

Check time slot is available for new appointment

I am working on schedule appointment booking application and want user want to schedule any appoint in between any time if any appoint already schedule.
For example one appoint is schedule from 10:30 to 11:30.
if new appoint is schedule like 11:00 to 11:30 than it should inform user time slot already book.

pls guide me how to write query to check time slot is available for new appointment or note. I have table which has start date/Time and end date/time filed.

Please provide sample data that include CREATE TABLE statements, INSERT statements and expected outcomes based on the provided data.

Slot ID StartTime EndTime
1 9:00 a.m. 10:00 a.m.
2 10:30 a.m. 11:30 a.m.
3 04:00 p.m 07:00 p.m

if any one want an appointment in between 11:00 - 11:30. than system should not allow user to appoint booking and prompt slot not avilalbe.
other tranasction 6:30 p.m. to 7:30 p.m. what will be result pls guide

Something like this perhaps:

with cte(starttime,endtime)
  as (select *
        from (values(cast('09:00am' as time),cast('11:30am' as time))
                   ,(cast('06:30pm' as time),cast('07:30pm' as time))
             ) as cte(starttime,endtime)
select distinct
      ,case when b.slotid is null then 'Free' else 'Busy' end as available
  from cte as a
       left outer join yourtable as b
                    on b.starttime<a.endtime
                   and b.endtime>a.starttime