Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Get start and end time for booked tiemslosts.

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
martgriff
Asked:
martgriff
  • 5
  • 4
1 Solution
 
dduserCommented:
Whats the Format of Starttime,Endtime and FromTime,ToTime???

Regards,

dduser
0
 
martgriffAuthor Commented:
They are all datetime length 8
0
 
dduserCommented:
What does date value consist of in case of Engineers Table??

Regards,

dduser
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
martgriffAuthor Commented:
the value is a time format. STARTTIME 08:00:00 ENDTIME 17:00:00
0
 
dduserCommented:
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
 
martgriffAuthor Commented:
Is there any way i can use my existing query to factor in the start and end date?
0
 
dduserCommented:
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
 
martgriffAuthor Commented:
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
 
dduserCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now