Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query to select non-overlapping times for given date

Posted on 2004-10-04
7
Medium Priority
?
460 Views
Last Modified: 2012-05-05
Hi,

I am working on a generic time-management system which will allow users to book items like equipment, staff, etc. for certain shifts during the day.  I have a check in the insert booking stored procedure to prevent overlapping times from being inserted, however I would like to limit the drop down list to only those shifts that won't overlap with the existing entries to avoid error message being returned.

There are two main tables being used here, the first is Shifts which stores the list of default shifts that can be assigned to an item, and is source of drop down list.

CREATE TABLE [dbo].[Shifts] (
      [ShiftID] [int] IDENTITY (1, 1) NOT NULL ,
      [Description] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
      [StartTime] [smalldatetime] NOT NULL ,
      [EndTime] [smalldatetime] NOT NULL
)

The second is the RotaCells table which stores the bookings for the items as per this listing.  The ShiftID column is a foreign key to the shifts table above to get shift start and end times.

CREATE TABLE [dbo].[RotaCells] (
      [CellID] [int] IDENTITY (1, 1) NOT NULL ,
      [ItemID] [int] NOT NULL ,
      [CellDate] [smalldatetime] NOT NULL ,
      [ShiftID] [int] NULL
)

This is the list of all possible shifts (at the moment)
1      08:30 - 17:30      1900-01-01 08:30:00      1900-01-01 17:30:00      1
2      17:30 - 01:30      1900-01-01 17:30:00      1900-01-01 01:30:00      1
3      01:30 - 08:30      1900-01-01 01:30:00      1900-01-01 08:30:00      1
5      06:00 - 14:00      1900-01-01 06:00:00      1900-01-01 14:00:00      1
6      10:00 - 17:00      1900-01-01 10:00:00      1900-01-01 17:00:00      1
25      08:30 - 13:00      1900-01-01 08:30:00      1900-01-01 13:00:00      1
26      07:00 - 09:30      1900-01-01 07:30:00      1900-01-01 09:30:00      1
27      15:30 - 18:00      1900-01-01 15:30:00      1900-01-01 18:00:00      1

If an item is booked on a date for shift 1 (08:30 - 17:30) then they should only be given option to work Shifts 2 and 3 to avoid any overlaps.  I hope this makes sense, if not just ask me to clarify.

Thanks for any help you can provide.
0
Comment
Question by:osullil
[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 70

Expert Comment

by:Scott Pletcher
ID: 12218384
I suggest rather than re-calculating overlaps for every item, you create a table, for example "ShiftOverlaps", and load/update this table once on the front-end anytime the Shift table changes (this code could, and probably should, be made a trigger).

Working on code now to create this table and implement it in a "search-for-available-resources" query.
0
 
LVL 1

Author Comment

by:osullil
ID: 12218460
The form in question is a pop-up and so will only ever be dealing with a single ItemID and Celldate at a time, which are passed to the stored procedure used to retreive list of available shifts.  
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12219357
can you post sample data and desired results?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 12220148
--Actual code to check for non-conflicting shifts

DECLARE @ItemId INT
SET @ItemId = 1

SELECT Shifts.*
FROM Shifts
-- don't select a shift that is already assigned (active) for this item
WHERE NOT EXISTS ( SELECT 1
      FROM RotaCells
      WHERE RotaCells.Itemid = @ItemId AND RotaCells.ShiftId = Shifts.ShiftId )
-- don't select a shift that conflicts with a shift already assigned for this item
AND Shifts.ShiftId NOT IN (
    SELECT ovl.OverlappingShift
    FROM ShiftOverlaps ovl
    WHERE ovl.AssignedShift IN (
            SELECT ShiftId
            FROM RotaCells
            WHERE RotaCells.Itemid = @ItemId ) )




Code to create and populate ShiftOverlaps table:

CREATE TABLE ShiftOverlaps (
      AssignedShift INT,
      OverlappingShift INT
)
TRUNCATE TABLE ShiftOverlaps
INSERT INTO ShiftOverlaps
SELECT assigned.ShiftId AS AssignedShift,
      overlapping.ShiftId AS OverlappingShift
FROM Shifts assigned
CROSS JOIN Shifts overlapping
WHERE assigned.ShiftId <> overlapping.ShiftId
AND ( assigned.StartTime BETWEEN overlapping.StartTime AND DATEADD(MINUTE, -1, overlapping.EndTime)
OR assigned.EndTime BETWEEN DATEADD(MINUTE, 1, overlapping.StartTime) AND overlapping.EndTime
OR overlapping.StartTime BETWEEN assigned.StartTime AND DATEADD(MINUTE, -1, assigned.EndTime)
OR overlapping.EndTime BETWEEN DATEADD(MINUTE, 1, assigned.StartTime) AND assigned.EndTime )
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12220228
Agree: some sample data and results would be very helpful for testing.
0
 
LVL 1

Author Comment

by:osullil
ID: 12224898
The ShiftOverlaps table looks like a good idea.  I like to minimise query overhead where possible and this would remove need for more complex query.  Here is some sample data as requested.

Shift Table Contents (ShiftID, Description, StartTime, EndTime, ActiveYN):
1     08:30 - 17:30     1900-01-01 08:30:00     1900-01-01 17:30:00     1
2     17:30 - 01:30     1900-01-01 17:30:00     1900-01-01 01:30:00     1
3     01:30 - 08:30     1900-01-01 01:30:00     1900-01-01 08:30:00     1
5     06:00 - 14:00     1900-01-01 06:00:00     1900-01-01 14:00:00     1
6     10:00 - 17:00     1900-01-01 10:00:00     1900-01-01 17:00:00     1
25     08:30 - 13:00     1900-01-01 08:30:00     1900-01-01 13:00:00     1
26     07:00 - 09:30     1900-01-01 07:30:00     1900-01-01 09:30:00     1
27     15:30 - 18:00     1900-01-01 15:30:00     1900-01-01 18:00:00     1

Rota Cells joined with shfits (CellID, ItemID, CellDate, ShiftID,Shifts.StartTime, Shifts.EndTime)
792      2      2004-10-03 00:00:00      1      1900-01-01 08:30:00      1900-01-01 17:30:00
817      2      2004-10-04 00:00:00      2      1900-01-01 17:30:00      1900-01-01 01:30:00
822      2      2004-10-04 00:00:00      1      1900-01-01 08:30:00      1900-01-01 17:30:00
794      2      2004-10-05 00:00:00      1      1900-01-01 08:30:00      1900-01-01 17:30:00
793      17      2004-10-06 00:00:00      3      1900-01-01 01:30:00      1900-01-01 08:30:00
795      2      2004-10-07 00:00:00      1      1900-01-01 08:30:00      1900-01-01 17:30:00
707      24      2004-10-11 00:00:00      1      1900-01-01 08:30:00      1900-01-01 17:30:00
786      24      2004-10-17 00:00:00      6      1900-01-01 10:00:00      1900-01-01 17:00:00

Expected Results:
Shifts for ItemID=2 AND Celldate=04/10/2004: ShiftID=3
Shifts for ItemID=24 AND Celldate=17/10/2004: ShiftID=2, 26
Shifts for ItemID=24 AND Celldate=11/10/2004: ShiftID=2, 3
Shifts for ItemID=17 AND Celldate=06/10/2004: ShiftID=1,2,6,25,27

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12227465
Thanks.

Btw, naturally you will have to add an @cellDate variable as well to handle the date.  And to keep the ShiftOverlaps table up to date, you really should put the code to build that table into a trigger on the Shifts table.  If you any further questions, please let me know.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

722 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