Solved

Set start and end times in booking system

Posted on 2006-10-19
7
263 Views
Last Modified: 2012-05-05
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
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
  • 4
  • 2
7 Comments
 
LVL 9

Expert Comment

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

Regards,

dduser
0
 

Author Comment

by:martgriff
ID: 17766911
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
                        
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17769841
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)
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17769854
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.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 17769953
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
0
 

Author Comment

by:martgriff
ID: 17772043
Thanks that second one works really well and does exactly what i wanted it to do.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17773718
Np, it was a very interesting q.
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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