Solved

Query to select non-overlapping times for given date

Posted on 2004-10-04
7
453 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
  • 4
  • 2
7 Comments
 
LVL 69

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 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 69

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 69

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 44
SSRS 2013 - Overlapping reports 2 21
TSQL - How to declare table name 26 30
Query group by data in SQL Server - cursor? 3 31
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

770 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