Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
comet
Starting Member
2 Posts |
 Posted - 2005-04-29 : 23:05:36
|
| I have a problem I don't know if I can do it in the SQL in 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.- LatestTimeThanks. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-30 : 06:51:18
|
| This is probably easiest if you have a table of all the possible start/end times of appointments then you can just join to that to get times that aren't taken.If an appointment can have any start/end time then it is a matter of getting the gaps between existing appointments.Again easier if you have a calendar table to give all the working days.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
comet
Starting Member
2 Posts |
Posted - 2005-05-02 : 10:17:13
|
| Thank you. I ended up writing a stored proc to use cursor to iterate thru the rows and process some logic. I think even if it can be done in 1 sql (doubtful :-) ), it would get so complex that it is hard to maintain. |
|
|
|
|
|
|
Scheduling SQL question