Solved

Get start and end time for booked tiemslosts.

Posted on 2006-11-02
9
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 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