CODEWITHSUNDEEP
sqlsql-servert-sql
Craig
Craig

Reputation: 36846

Query to find a block of time in a schedule

Imagine I had a work schedule from 9am to 6pm. It is divided into 15 minute blocks and appointments (increments of 15 minutes) can be fitted into the times available.

Now, if I need to insert a new appointment that is 45 minutes long is there an easy query to find a block of time that is available to fit the appointment in for a given date

The basic table design is

AppointmentId
Date
StartTime
Length - 15 minute incremenents

I would like to get a list of available times to choose from, so if the only appointment for the given day is a 30 minute one at 9:30 then the list of times would be

(No times before 9:30 as the 45 minute appointment wont fit)
10:15
10:30
10:45
...
5:15pm (last time of the day the appointment will fit)

Upvotes: 0

Views: 367

Answers (1)

Reza
Reza

Reputation: 19913

By using ranking function (i.e Row_Number()) set number for each row in each day (let say it's name is rn), then join this query with it self by this condition q2.rn = q1.rn-1 then you have end of appointment beside start of next appointment, then calculate datediff(mi) on this end and start, so this value is the gap, then write another query wrapping this query to filter records that have gap >= yourNeededTime. Also for start of day and end of day you can create 2 dummy records one for 9am and one for 6pm so that yo can handle gap of start of day to the first appointment and last appointment to the end of day.

I hope this helps

Upvotes: 1

Related Questions