Scheduling SQL

[pikachiu]

I have a problem I don't know if I can do it in the SQL server. I have an "Appointment" table that contains Name, StartDateTime and StopDateTime. Each row represents one appointment. When I want to schedule a new appointment, I want to find the next N available time slot. I think this is too complex to write in an sql statement. Is it possible to write a stored prod?

The function has a few parameters:
- DateTimeToStartSearching (e.g. I want to start searching today)
- NumberOfMinutesForNewAppointment (new appointment requires 30 minutes)
- NumberOfAvailableTimeSlotsToReturn (wants 10 options to be returned)
- EarliestTime (e.g. we want the appointment to be in the afternoon, then EarliestTime is 1pm and LatestTime is say 5pm.
- LatestTime

 

[NoCoolHandle]

The trick is to write a co-related subquery...

They are a bit bizare, but the concept is very much the same as row by row processing.. THe idea is that the inner query will check each row against the outer query...

For instance...

Create table x
(c1 int not null)
go
insert into x 
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 7 union all
select 8 
go
Select min(c1)+1 from  x   OuterQuery  where not exists 
(Select * from x InnerQuery  where InnerQuery.c1  = OuterQuery.c1 + 1)
go
Drop Table x

The preceeding query will return the first "Gap" in the sequence...

The idea is that the "Not Exists" clause is looking to see if a value in the inner query can find a row 1 greater in the outer query. If it doesn't then that row returns true...

In the Outer Query's Selec you need to add 1 to the value returned as this is the missing value..

HTH


Rob

PS to get all holes remove the Min in the outer query...

i.e. If you test to see if 5-1 (4) exists in

 

[pikachiu]

Thank you. I think I got the idea. I will try to compose the scheduling query using the subquery.

 

[pikachiu]

This is what I did but it returns all the time slots for me even the gap (next StartDateTime - current EndDate Time) is less than the appointment duration time! Could someone please help?

*** 240 is 4 hours duration

SELECT EndDate
FROM Appointments currentApp
WHERE EXISTS
(SELECT *
FROM appointments nextApp
WHERE datediff(minute, nextApp.startdate, currentApp.enddate) > 240 AND NOT EXISTS
(SELECT *
FROM appointments nextApp1
WHERE nextApp1.startdate < nextApp.startdate
AND datediff(minute, nextApp1.startdate, currentApp.enddate) > 240
)
)
ORDER BY EndDate

 

[NoCoolHandle]

Change
[blue]SELECT EndDate
FROM Appointments currentApp
[/blue]
to
[blue]SELECT min(EndDate) as EndDate
FROM Appointments currentApp
[/blue]