Scheduling sql
-
I have a problem I don't know if I can do it 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.
- LatestTime
-
You can write a stored procedure that accepts parameters. You could also use dynamic SQL and create your SQL statements on the fly. The are numerous articles on the pros and cons of using dynamic SQL on the web. BOL contains information on writing stored procedures.
The complexity of the SQL statements will be the same. As the only difference will be the manner in which you insert your variables into the SELECT statement.
Your business rules will have a major impact on how complex these statements have to be to meet your requirements.
For instance a rule that states that the start time for an appointment must be on the hour or ½ hour i.e. 9 or 9:30 is ok but 9:03 or 9:24 are not valid start times will require different logic than a rule that states appointment start time and length of the appointment will be set at when the appointment is set and customers must not exceed their time length. Then an appointment could be set at 9:03 and concluded at 9:38 leaving the next available start time at 9:39 (you have to give them a whole minute to get out the door or do you
). A little more information would be helpful.
HTH
Mike
-
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. You have a good point about 9:03. I missed that ... yet another rule! 🙁
-
Can we see the cursor code... I'd like to give a shot at the set based approach.
-
Pretty sure you don't need a cursor for that!
* Noel -
Edit Added code to remove Lunch times from appointment times..Never Ever tell the boss he can not take a lunch break.
Set based approach.
HTH
Mike
/*
Create a table with all possible appointments (#AppointmentTimes)
For for a production app consider
pinning this table in memory to speed up access see Remi Gregoire's post
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=179284&p=2
Assumptions:
Appointments are for 30 min
Workday starts at 9:00 AM and ends at 5:00PM (First appointment starts at 9 Am
last Appointment starts at 4:30 PM)
Lunch is for 1 hour from 12:00 to 1:00 PM
Business is open Monday thru Friday (no holidays in this example)
No appointment will be made more than 1 year in advance
There is an employee table where EmployeeID is PK
There is a Customer Table where CustomerID is PK
AppointmentTimes Table is a lookup table containing all possible appointments
Maintenance:
Set Cascade updates, cascade deletes linked to AppointmentsSet
Develope a stored proc triggered on the first of the month
delete rows for previous month. insert new data (previous month + 1 year)
, delete holidays (will need a table containing holiday information)UPDATE
AppointmentsSet Table from AppointmentTimes
AppointmentsSet Table Contains:
EmployeeID FK to employee table
CustomerID FK to Customer Table a value of -1 indicates that no record exist for customer
AppointmentTime FK to AppointmentTimes
AppointmentSet Value of 0 indicates open
Value > 0 indicates appointment is set
Value < 0 indicates no appointment vacation or out of office
Used to track reasons employee was not in office
SETUP:
For each employee load AppointmentsSet Table with all possible appointments
4160 records per employee.
CHECKING FOR OPEN APPOINTMENTS LOGIC:
Select 2 full days worth of open appointments between the desired date
and 180 days (6 months) after the desired date
SELECT TOP 32 DateName(dw,b.Apt)AS "Day",
Convert(Char(17),B.Apt)AS "Open Dates"
FROM #AppointmentsSet AS a INNER join #appointmentTimes AS b
ON a.AppointmentTime = B.PKNumber
WHERE a.EmployeeID = @EmployeeID
AND b.Apt BETWEEN @DesiredDate AND DateAdd(dd,180,@DesiredDate)
AND a.AppointmentSet = 0
ADDING AN APPOINTMENT FOR AN EMPLOYEE
UPDATE #AppointmentsSet
SET AppointmentSet = 1
FROM #AppointmentsSet as a INNER JOIN #AppointmentTimes as b
ON a.AppointmentTime = b.PKNumber
WHERE a.EmployeeID = @EmployeeID
AND b.Apt =@DesiredDate
*/
CREATE TABLE [#AppointmentTimes]
(
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
[Apt] [datetime]
)
CREATE TABLE [#AppointmentsSet]
(
[PkNumber]int NULL, --
[EmployeeID] int NOT NULL, --FK to sales person
[AppointmentTime]int NULL, --FK PKNumber from AppointmentTimes table
[AppointmentSet]int,--A Value of -1 = vacation value of 1 = appointment set
[CustomerID] int NULL --FK to customer data a 0 value indicates a new customer
)
GO
--Load AppointmentTimes Table
Declare @i int
Declare @OuterLoop int
Declare @Dummy DateTime
set @i = 0
set @OuterLoop = 0
set @Dummy = 'Jan 1, 2005 08:30 AM'
While @OuterLoop < 365
begin
while @i < 16
begin
set @i = @i+1
IF (DatePart(dw,@Dummy) >1 and DatePart(dw,@Dummy) < 7)
Begin
set @Dummy =DateAdd(mi,30,@dummy)
--add logic for lunch
IF(DatePart(hh,@Dummy) <> 12)
Begin
INSERT INTO #AppointmentTimes (Apt) values(@Dummy )
End
end
end
set @OuterLoop = @Outerloop +1
set @Dummy ='Jan 1, 2005 08:30 AM'
set @Dummy = DateAdd(dd,@OuterLoop,@Dummy)
set @i=0
end
go
--Add data for employee # 1 in AppointmentsSet Table
DECLARE @Count int
Set @Count=0
While @Count < 4160
Begin
set @Count = @Count + 1
INSERT INTO #AppointmentsSet (PKNumber,AppointmentTime,EmployeeID,AppointmentSet)
Values(@Count,@Count,1,0)
End
GO
--Test
DECLARE @DesiredDate DateTime
DECLARE @EmployeeID int
Set @DesiredDate ='1/5/2005 9:00AM'
Set @EmployeeID = 1
--Add some appointments for test
UPDATE #AppointmentsSet
SET AppointmentSet = 1
FROM #AppointmentsSet as a INNER JOIN #AppointmentTimes as b
ON a.AppointmentTime = b.PKNumber
WHERE a.EmployeeID = @EmployeeID
AND b.Apt BETWEEN '1/5/2005 10:00AM' AND '1/5/2005 11:30am'
--Check for an appointment
SELECT TOP 32 DateName(dw,b.Apt)AS "Day",
Convert(Char(17),B.Apt)AS "Open Dates"
FROM #AppointmentsSet AS a INNER join #appointmentTimes AS b
ON a.AppointmentTime = B.PKNumber
WHERE a.EmployeeID = @EmployeeID
AND b.Apt BETWEEN @DesiredDate AND DateAdd(dd,180,@DesiredDate)
AND a.AppointmentSet = 0
/*
--Result set
Wednesday Jan 5 2005 9:00
Wednesday Jan 5 2005 9:30
Wednesday Jan 5 2005 12:00
.....
*/
DROP TABLE #AppointmentTimes
DROP TABLE #AppointmentsSet
-
Wow, this is wonderful but I need time to digest it. 🙂 Thanks everyone for your input. Special thanks for Michael Du Bois for your thorough and well documented solution. This sure is very helpful
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply