Solved

Get start and end time for booked tiemslosts.

Posted on 2006-11-02
9
280 Views
Last Modified: 2008-02-01
I have the below query which pulls out free time slots based on the booked slots. However it does not factor in the start and end times if they are not already a booked apointment. So if the first appointment booked is at 10-11am, yet the engineer starts their day at 8am it dosent show that there is a free time from 8-10. It will only show a free apointment from 11am.

I have two tables Schedule and engineers. The engineers one will have their start and end time and the schedule will hold the booked appointments. They are linked by the engineer. How can i factor in the start and end times into by query.

Tables: -
egineers: engineers, STARTTIME, ENDTIME, ACTIVE
Scedule: IDNum, Engineer, FromTime, ToTime, BookedDate



SELECT     s1.IDNum, s1.Engineer, s1.ToTime AS FromTime, s2.FromTime AS ToTime, s1.BookedDate
FROM         dbo.Schedule s1 INNER JOIN
                      dbo.Schedule s2 ON s2.IDNum - s1.IDNum = 1 AND DATEDIFF(hh, s1.ToTime, s2.ToTime) > DATEDIFF(hh, s1.FromTime, s1.FromTime)
WHERE     (s1.BookedDate <= DATEADD([HOUR], 90, CONVERT(DATETIME, '2006-10-10 00:00:00', 103)))
0
Comment
Question by:martgriff
  • 5
  • 4
9 Comments
 
LVL 9

Expert Comment

by:dduser
ID: 17857181
Whats the Format of Starttime,Endtime and FromTime,ToTime???

Regards,

dduser
0
 

Author Comment

by:martgriff
ID: 17857220
They are all datetime length 8
0
 
LVL 9

Expert Comment

by:dduser
ID: 17857228
What does date value consist of in case of Engineers Table??

Regards,

dduser
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:martgriff
ID: 17857241
the value is a time format. STARTTIME 08:00:00 ENDTIME 17:00:00
0
 
LVL 9

Accepted Solution

by:
dduser earned 500 total points
ID: 17857293
Following procedure would not be exactly what you want, but it does what you are trying to find. I have assumed timeslots to be hourly basis.

Regards,

dduser



Create Procedure TempProc As

Create #TempTable (Engineer as varchar(30),StartSlotTime as DateTime,EndSlotTime as DateTime)

Declare @StartTime as DateTime
Declare @EndTime as DateTime
Declare @Engineer as varchar(30)
Declare @Cnt as Int

Declare EngineerCursor CURSOR for Select Engineer, StartTime,EndTime from Engineers where Active ='Yes'

Open EngineerCursor

Fetch Next from EngineerCursor into @Engineer,@StartTime,@EndTime

While @@Fetch_Status = 0
Begin

            
                While(@StartTime <> @EndTime)
            Begin
                  Set @Cnt = 0
                  Set @Cnt = Select count(*) from Schedule where Engineer = @Engineer and @starttime between fromtime and totime
                  If @cnt = 0
                        Insert into #TempTable values(@Engineer,@StartTime,DateAdd(hh,@StartTime,1)
                  @StartTime = DateAdd(hh,@StartTime,1)
            End
Fetch Next from EngineerCursor into @Engineer,@StartTime,@EndTime                  

End

Select * from #TempTable

Return
0
 

Author Comment

by:martgriff
ID: 17857325
Is there any way i can use my existing query to factor in the start and end date?
0
 
LVL 9

Expert Comment

by:dduser
ID: 17857340
Suppose engineer is working between 8:00 to 16:00, his schedule is fixed between 9:00 to 11:00 and 13:00 to 6:00. So the output should be 8:00 to 9:00 and 11:00 to 13:00 right??

As the query you are using will give only one slot free???

Please confirm this.

Regards,

dduser
0
 

Author Comment

by:martgriff
ID: 17857671
the query i have used currently only pulls out the fee times slots based on the ones that re booked. So if the engineer was booked from 9:00 to 11: 00 and  13:00 to 14:00 it will only show the fee timeslot 11:00 to 13:00. It does not recoginise that the engineers start time is 8:00 and end time 17:00 which it gets from teh engineers table.
0
 
LVL 9

Expert Comment

by:dduser
ID: 17857766
Thats the reason it has to run in the loop of Engineers Start Time & End Time and within that time it has to find out slots which are booked, thats what i have done in the Procedure.

Regards,

dduser
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question