Query to select non-overlapping times for given date

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.
LVL 1
osullilAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
--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
 
Scott PletcherSenior DBACommented:
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
 
osullilAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mcp111Commented:
can you post sample data and desired results?
0
 
Scott PletcherSenior DBACommented:
Agree: some sample data and results would be very helpful for testing.
0
 
osullilAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
All Courses

From novice to tech pro — start learning today.