Solved

Get start and end time for booked tiemslosts.

Posted on 2006-11-02
9
277 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 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