Solved

Query to select non-overlapping times for given date

Posted on 2004-10-04
7
452 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:ScottPletcher
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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:ScottPletcher
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select Statement 2 20
TSQL previous 5 22
Sql Join Problem 2 22
SQL - SP needs a little help 9 19
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now