Solved

Query to select non-overlapping times for given date

Posted on 2004-10-04
7
454 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Location of Dynamics AX Service accounts in SQL 3 16
Query for timesheet application 3 21
too many installs coming along with SQL 2016? 1 18
MS SQL + group by time 4 15
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

820 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