Solved

Get start and end time for booked tiemslosts.

Posted on 2006-11-02
9
270 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
 

Author Comment

by:martgriff
ID: 17857241
the value is a time format. STARTTIME 08:00:00 ENDTIME 17:00:00
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now