Scheduling Problem
-
Hi all What I want is to be able to return open times that will accomidate an appointment
that will last a variable amount of time. The amount of time between the ending of one
appointment and the beginning of the next appointment would have to = or be greater than the desired length of the appointment.
Rules Open at 9 close at 5 lunch 12 to 1, Appointments can start and end only on 10 min schedules 9:00, 9:10 ...4:50
Data
Apt StartDate EndDate
1 9:10 9:30
2 10 10:40
Results showes open appt at 9 with next apt at 9:40 which is correct for 1st apt
Results showes open appt at 9:50 with next apt at 10:50 which is correct 2nd apt
For example with the above data if the desired
appointment length was 60 min then the Open Times would be 10:50, 11:00, 1:00 ...4:00.
A start time of 11:10 would run into the lunch hour and if the appointment started after 4:00
PM there would not be enough time to complet the appointment before close of business at 5
For example with the above data if the desired
appointment length was 60 min then the Open Times would be 10:50, 11:00, 1:00 ...4:00.
A start time of 11:10 would run into the lunch hour and if the appointment started after 4:00
PM there would not be enough time to complet the appointment before close of business at 5
Thanks in Advance of any help you can give me
Mike
/*==================================================================================
Set up stuff
===================================================================================*/
IF Object_ID('TempDB..#AptEnd') >0
DROP TABLE #AptEnd
IF Object_ID('TempDB..#AptStart') >0
DROP TABLE #AptStart
IF Object_ID('TempDB..#AppointmentTimes') > 0
DROP TABLE #AppointmentTimes
CREATE TABLE [#AppointmentTimes]
(
[PkNumber] int IDENTITY (1, 1) NOT NULL ,
[Apt] datetime
)
CREATE TABLE #AptEnd
(
[PK] [int] NULL ,
[EndDate] [datetime] NULL ,
[FKAppointment] [int] NULL
)
CREATE TABLE #AptStart
(
[PK] [int] NULL ,
[StartDate] [datetime] NULL ,
[FKAppointment] [int] NULL
)
/*==========================================================================================
Load Test Data
===========================================================================================*/
Declare @InnerLoop int
Declare @OuterLoop int
Declare @Dummy DateTime
set @InnerLoop = 0
set @OuterLoop = 0
--When setting Dummy time must be 10 min before business opens
--Load for current month for test 1st work day = May 2
DECLARE @DMonth Char(2), @DDay Char(2),@DYear Char(4),@StartTime Char(7),@DummyDate Char(20)
SET @DMonth = DatePart(Month,GetDate())
SET @DDay = '1' --this is a constant
SET @DYear = DatePart(Year,GetDate())
SET @StartTime = '8:50AM'
SET @DummyDate = @DMonth + '/' + @DDay + '/' + @DYear + ' ' + @StartTime
SET @Dummy = @Dummydate
Set NOCount Off
While @OuterLoop < 90 --KISS Just 3 months
begin
while @InnerLoop < 48 --=number of hours open - lunch (* 6)
begin
set @InnerLoop = @InnerLoop + 1
--insert only weekdays mon - Fri
IF (DatePart(dw,@Dummy) > 1
AND DatePart(dw,@Dummy) < 7)
Begin
set @Dummy =DateAdd(mi,10,@dummy)
--Remove lunch times 12 to 1
IF(DatePart(hh,@Dummy))<> 12
Begin
INSERT INTO #AppointmentTimes (Apt)
VALUES (@Dummy )
End
end
end --Inner loop
set @OuterLoop = @Outerloop +1
set @Dummy = @DummyDate
set @Dummy = DateAdd(dd,@OuterLoop,@Dummy)
set @InnerLoop = 0
end --Outer Loop
Set NOCount On
GO
use test
--Load Appointment test data
DECLARE @TestData DateTime
Set @TestData = '6/9/2005 9:10:00AM'
INSERT INTO #AptStart (PK,StartDate,FKAppointment)
Values(1,@TestData,1)
Set @TestData = '6/9/2005 9:30AM'
INSERT INTO #AptEnd (PK,EndDate,FKAppointment)
Values(1,@TestData,1)
Set @TestData = '6/9/2005 10:00AM'
INSERT INTO #AptStart (PK,StartDate,FKAppointment)
Values(2,@TestData,1)
Set @TestData = '6/9/2005 10:40AM'
INSERT INTO #AptEnd (PK,EndDate,FKAppointment)
Values(2,@TestData,1)
/*========================================================================================
End Loading Test Data
=========================================================================================*/
GO
DECLARE @DesiredTime DateTime
SET @DesiredTime =Convert(dateTime,'6/9/2005 9:00AM')
SELECT Top 15
Convert(char(20),A.Apt)AS "Open Dates"
FROM
#AppointmentTimes AS A
WHERE
NOT EXISTS
(
SELECT
B.StartDate
FROM
#AptStart AS B
JOIN
#AptEnd AS E
ON
E.PK = B.Pk
WHERE
A.Apt Between b.StartDate And E.EndDate
 
AND
A.Apt >=@DesiredTime
Order By A.Apt
/*==============================================================================
Edited to add the order by clause. I think I flunked sand box again. Can not get that cut and paste thingy to work
==================================================================================*/
-
*sigh* I wrote a heap, but my browser seems to have lost it. I hit 'post reply', but it didn't go through for some reason.
Anyway... the upshot of it was:
Have a table tblDateTimes, which has a tightly packed, clustered indexed field [thedatetime].
Have a table tblAppointments, which includes indexed fields startdate, enddate. Put evenings, weekends, holidays, lunch-breaks, etc, in there as existing appointments - perhaps with a flag to say that they are 'leave' appointments.
Use a query like:
select top 15 ds.thedatetime avail_start, de.thedatetime avail_end
from dbo.tblDateTimes ds
join dbo.tblDateTimes de
on de.thedatetime = dateadd(minute,@apptlength,ds.thedatetime)
where ds.thedatetime >= @desiredtime
and not exists (
select * from tblAppointments a
where a.startdate < de.thedatetime
and ds.thedatetime < a.enddate
)
order by ds.thedatetime
Does this help?
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Rob I just hate it when my computer eats my stuff.
been there done that . If you don't preview the post it seems to go into cyper space. I'm sure that todays school kids use this in the same manner I used the excuse "the dog ate my homework". Thanks for your suggestion I will test it later today.Mike
-
Replaces Previous Post
Ron Thanks that is real close. Using my previous posed dat and when scheduling for a 1 hour appointment returns 10:40 and 10:50 which gets us to lunch at 12. there are no results for after lunch but that may be an easy fix.
Thanks
Mike
-
Ron you are the man it works like a charm. The easy fix was my changing my code from Top 2 to top 20 (which is what I though it was)
Blame it on my fat fingers.Thanks so much
Mike
-
hehe... I frequently get 'Rod', but rarely 'Ron'. I guess the proximity of 'b' and 'n' on the keyboard helps.
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Rob I' not dyslexic (typing d for b) I just can't type it's those fat fingers of mine the same ones that typed 2 for 20. Now they are typing n for b.
Thanks again for your help.
Mike
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply