We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

# Set start and end times in booking system

on
Medium Priority
306 Views
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.

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

SELECT DISTINCT
TOP 100 PERCENT S1.IDNum, S1.Engineer, S1.ToTime AS FromTime, S2.FromTime AS ToTime, S1.BookedDate, dbo.tbl_engineer.STARTTIME,
dbo.tbl_engineer.ENDTIME
FROM         dbo.Schedule S1 INNER JOIN
dbo.Schedule S2 ON S1.ToTime < S2.FromTime INNER JOIN
dbo.tbl_engineer ON S1.Engineer = dbo.tbl_engineer.ENGINEER
WHERE     (S2.FromTime =
(SELECT     MIN(x.Fromtime)
FROM          Schedule AS x
WHERE      x.FromTime > s1.ToTime)) AND (NOT EXISTS
(SELECT     x.Fromtime
FROM          Schedule AS x
WHERE      x.FromTime = s1.ToTime)) AND (S1.BookedDate >= CONVERT(DATETIME, '10/10/2006', 103))
ORDER BY S1.BookedDate, S1.FromTime
Comment
Watch Question

## View Solution Only

Commented:
How do you store your starttime, endtime in Engineers & Schedule Table - FromTime, ToTime. I would require the format.

Regards,

dduser

Commented:
Below is how i have stored the data in teh two tables that i am using. Thanks

Date in Schedule table: -

IDNum      FromTime      ToTime      BookedDate      Engineer
1      09:00:00      10:00:00      10/10/2006      Eng1
2      12:00:00      13:00:00      10/10/2006      Eng2
3      15:00:00      17:00:00      10/10/2006      Eng2
4      08:00:00      10:00:00      11/10/2006      Eng1
5      12:00:00      13:00:00      11/10/2006      Eng1
6      15:00:00      17:00:00      11/10/2006      Eng1

Data in engineer table: -

Engineer        STARTTIME         ENDTIME
Eng1                  08:00:00           17:00:00

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
The code below handles the end-of-day times, plus corrects some bugs in the original code.  Still trying to figure out an efficient way to get the start-of-day unbooked time.

SELECT unbooked.Engineer, unbooked.BookedDate,
unbooked.FromTime, ISNULL(unbooked.ToTime, eng.EndTime) AS ToTime
FROM (
SELECT IDNum, Engineer, BookedDate, ToTime AS FromTime,
(SELECT MIN(fromTime)
FROM  schedule x
WHERE x.engineer = s1.engineer
AND   x.bookedDate = s1.bookedDate
AND   x.fromTime >= s1.ToTime) AS ToTime
FROM schedule s1
WHERE bookedDate >= CONVERT(DATETIME, '10/10/2006', 103)
) AS unbooked
INNER JOIN tbl_engineer eng ON eng.engineer = unbooked.engineer
WHERE unbooked.FromTime <> unbooked.ToTime
OR    (unbooked.ToTime IS NULL AND unbooked.FromTime < eng.EndTime)
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Most efficient is probably using a temp table to store the above result, then doing the final selection from the temp table.  But I wasn't sure if you were willing to use a temp table or not.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
I think this gets the start times, although it requires extra table scans and joins:

SELECT unbooked.Engineer, unbooked.BookedDate,
unbooked.FromTime, ISNULL(unbooked.ToTime, eng.EndTime) AS ToTime
FROM (
SELECT Engineer, BookedDate, ToTime AS FromTime,
(SELECT MIN(fromTime)
FROM  schedule x
WHERE x.engineer = s1.engineer
AND   x.bookedDate = s1.bookedDate
AND   x.fromTime >= s1.ToTime) AS ToTime
FROM schedule s1
WHERE bookedDate >= CONVERT(DATETIME, '10/10/2006', 103)
UNION ALL
SELECT e2.Engineer, s2.BookedDate, e2.StartTime AS FromTime, MIN(s2.FromTime) AS ToTime
FROM tbl_engineer e2
INNER JOIN schedule s2 ON s2.engineer = e2.engineer
WHERE s2.bookedDate >= CONVERT(DATETIME, '10/10/2006', 103)
GROUP BY e2.Engineer, s2.BookedDate, e2.StartTime
HAVING MIN(s2.FromTime) > e2.StartTime
) AS unbooked
INNER JOIN tbl_engineer eng ON eng.engineer = unbooked.engineer
WHERE unbooked.FromTime <> unbooked.ToTime
OR    (unbooked.ToTime IS NULL AND unbooked.FromTime < eng.EndTime)
ORDER BY unbooked.BookedDate, unbooked.FromTime

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
Thanks that second one works really well and does exactly what i wanted it to do.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Np, it was a very interesting q.
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile

OR

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the