Solved

Find overlapping times in sql

Posted on 2014-10-12
10
120 Views
Last Modified: 2014-10-17
I have a table that has two time columns.  HourTimeFrom and HourTimeTo.  I enter the times for activities in those columns as to when the begin and end.

I am trying to find overlapping times between records to see if there is duplication..  The code I use to find overlapping times is:

select count(*) from tblOrgHours where RegID = 74 And ActivityDate = '07/01/2014' And 
                   ((HourTimeFrom >= '09:00:00.0000000' And HourTimeFrom <= '09:00:00.0000000') Or 
                   (HourTimeTo >= '10:00:00.0000000' And HourTimeTo <= '10:00:00.0000000') Or 
                   (HourTimeFrom < '09:00:00.0000000' And HourTimeTo > '10:00:00.0000000'))

Open in new window


Am I on the right track to find those overlapping times with the above formula?
0
Comment
Question by:al4629740
[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
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 40376349
Hi,
List data and check a record for sample. I think that it is wrong. You must define self join with your tables:

SELECT * 
FROM tblOrgHours  tb1, tblOrgHours tb2
WHERE tb2.HourTimeFrom <= tb.HourTimeTo
AND (tb2.HourTimeTo IS NULL OR tb2.HourTimeTo >= tb1.HourTimeFrom )  AND
tb1.RegID = 74 And tb1.ActivityDate = '07/01/2014' And tb2.RegID = 74 And tb2.ActivityDate = '07/01/2014'

Open in new window

-FA
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40376357
No. You have 3 conditions and the first 2 aren't effective

( >= 09:00 and <= 09:00 ) means "is equal to 09:00")
(>= 10:00 and <= 10:00)  means "is equal to 10:00")

Think of the comparisons needed this way:
    LOW                 HIGH
-----|-------------------|-----
     |                   |
S-----------E            |          starts before, ends in
     | S---------------E |          starts in, ends in
     |    S----------------------E  starts in, ends after
     |                   |
S--------------------------------E  spans
     |                   |

            S < HIGH
            E > LOW

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 40376378
Provide some sample data and expected result.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40376494
0
 

Author Comment

by:al4629740
ID: 40377032
But if I use
( >= 09:00 and <= 09:00 ) means "is equal to 09:00")
(>= 10:00 and <= 10:00)  means "is equal to 10:00")  
with
(HourTimeFrom < '09:00' And HourTimeTo > '10:00'))

Won't that fix the problem?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40377160
>>Won't that fix the problem?<<
Not sure what the problem is. Can you provide some sample data illustrating the problem with records that both meet and not meet the criteria for the problem?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40377170
Also, what version of sql server are you using? With sql server 2012, lead and lag functions were introduced which may assist in finding a solution.
0
 

Author Comment

by:al4629740
ID: 40377209
sql 2012

I have a table that has many records in it as attached.  I need to look in the table and check for any overlapping records with new entries.

For example, if I enter a new record from 9:00am to 10:00am then the following sql query is executed to see if there is a count greater than 0.  If the count is greater then 0 then I know there is an overlapping record and the new record will not be executed.

select count(*) from tblOrgHours where RegID = 74 And ActivityDate = '07/01/2014' And 
                   ((HourTimeFrom >= '09:00:00.0000000' And HourTimeFrom <= '09:00:00.0000000') Or 
                   (HourTimeTo >= '10:00:00.0000000' And HourTimeTo <= '10:00:00.0000000') Or 
                   (HourTimeFrom < '09:00:00.0000000' And HourTimeTo > '10:00:00.0000000'))

Open in new window

sample2.xlsx
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40383813
Testing for overlaps, but where a start time can be equal to a finish time
   base                base
 HourTimeFrom        HourTimeTo
-----|-------------------|-----
     |                   |
S-----------E            |          starts before, ends in
     | S---------------E |          starts in, ends in
     |    S----------------------E  starts in, ends after
S--------------------------------E  starts before, ends after
     |                   |

            S < base.HourTimeTo
            E > base.HourTimeFrom

Open in new window


select * 
from tblOrgHours base
where exists ( select null 
               from tblOrgHours comp 
               where comp.HourID <> base.HourID  --<< don't compare to itself
              
               and comp.ActivityDate = base.ActivityDate --<< match via these fields, mat need adjustment
               and comp.Agency = base.Agency
               and comp.Program = base.Program
               and comp.RegID = base.RegID

               and comp.HourTimeFrom < base.HourTimeTo   --<< test for overlaps part1
               and comp.HourTimeTo   > base.HourTimeFrom --<< test for overlaps part2
              )
;

Open in new window

On a small extract from your spreadsheet (for just agency='Administrator')  this is the result:
| HOURID |        AGENCY | AGENCYID | PROGRAM | ACTIVITYID | REGID |                ACTIVITYDATE | HOURS | HOURTIMEFROM | HOURTIMETO | PARTICIPANTHOUR | VOLUNTEERHOUR | FISCAL |                   ENTRYTIME |
|--------|---------------|----------|---------|------------|-------|-----------------------------|-------|--------------|------------|-----------------|---------------|--------|-----------------------------|
|    205 | Administrator |       74 |     CYD |          3 |    78 | July, 07 2014 00:00:00+0000 |     1 |     09:00:00 |   10:00:00 |               0 |             1 |   2015 | July, 07 2014 10:38:16+0000 |
|    219 | Administrator |       74 |     CYD |          3 |    78 | July, 07 2014 00:00:00+0000 |     8 |     09:00:00 |   17:00:00 |               0 |             1 |   2015 | July, 07 2014 12:32:59+0000 |
|    242 | Administrator |       74 |     CSW |          1 |     4 | July, 03 2014 00:00:00+0000 |     1 |     09:00:00 |   10:00:00 |               0 |             1 |   2015 | July, 08 2014 19:07:08+0000 |
|    243 | Administrator |       74 |     CSW |          1 |     4 | July, 03 2014 00:00:00+0000 |     1 |     09:00:00 |   10:00:00 |               0 |             1 |   2015 | July, 08 2014 19:07:12+0000 |
|    244 | Administrator |       74 |     CSW |          1 |     4 | July, 03 2014 00:00:00+0000 |     1 |     09:00:00 |   10:00:00 |               0 |             1 |   2015 | July, 08 2014 19:07:23+0000 |

Open in new window

See this at: http://sqlfiddle.com/#!3/5a3fa/3
Details:
CREATE TABLE tblOrgHours 
	([HourID] int, [Agency] varchar(19), [AgencyID] int, [Program] varchar(7), [ActivityID] int, [RegID] int, [ActivityDate] datetime, [Hours] int, [HourTimeFrom] varchar(8), [HourTimeTo] varchar(8), [ParticipantHour] int, [VolunteerHour] int, [Fiscal] int, [EntryTime] datetime)
;
	
INSERT INTO tblOrgHours 
	([HourID], [Agency], [AgencyID], [Program], [ActivityID], [RegID], [ActivityDate], [Hours], [HourTimeFrom], [HourTimeTo], [ParticipantHour], [VolunteerHour], [Fiscal], [EntryTime])
VALUES
	(180, 'Administrator', 74, 'CSW', 3, 35, '2014-07-03 00:00:00', 1, '09:00:00', '10:00:00', 1, 0, 2015, '2014-07-03 10:23:32'),
	(181, 'Administrator', 74, 'CSW', 3, 9, '2014-07-03 00:00:00', 1, '09:00:00', '10:00:00', 1, 0, 2015, '2014-07-03 10:23:32'),
	(183, 'Administrator', 74, 'CSW', 3, 6, '2014-07-03 00:00:00', 1, '09:00:00', '10:00:00', 1, 0, 2015, '2014-07-03 10:23:32'),
	(187, 'Administrator', 74, 'CSW', 3, 1, '2014-07-03 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-03 10:23:32'),
	(190, 'Administrator', 74, 'CYD', 4, 8, '2014-07-06 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-06 23:13:03'),
	(193, 'Administrator', 74, 'CSW', 3, 6, '2014-07-06 00:00:00', 4, '09:00:00', '10:00:00', 1, 0, 2015, '2014-07-06 23:17:09'),
	(196, 'Administrator', 74, 'CYD', 3, 35, '2014-07-07 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 10:33:23'),
	(199, 'Administrator', 74, 'CYD', 3, 14, '2014-07-07 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 10:38:16'),
	(200, 'Administrator', 74, 'CYD', 3, 4, '2014-07-07 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 10:38:16'),
	(202, 'Administrator', 74, 'CYD', 3, 1, '2014-07-07 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 10:38:16'),
	(203, 'Administrator', 74, 'CYD', 3, 8, '2014-07-07 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 10:38:16'),
	(204, 'Administrator', 74, 'CYD', 3, 6, '2014-07-07 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 10:38:16'),
	(205, 'Administrator', 74, 'CYD', 3, 78, '2014-07-07 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 10:38:16'),
	(206, 'Administrator', 74, 'CYD', 3, 12, '2014-07-07 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 10:38:16'),
	(209, 'Administrator', 74, 'CSW', 1, 35, '2014-07-02 00:00:00', 1, '11:00:00', '12:00:00', 1, 0, 2015, '2014-07-07 10:45:44'),
	(210, 'Administrator', 74, 'CSW', 1, 9, '2014-07-02 00:00:00', 1, '11:00:00', '12:00:00', 1, 0, 2015, '2014-07-07 10:45:44'),
	(212, 'Administrator', 74, 'CSW', 1, 6, '2014-07-02 00:00:00', 1, '11:00:00', '12:00:00', 1, 0, 2015, '2014-07-07 10:45:44'),
	(216, 'Administrator', 74, 'CYD', 3, 14, '2014-07-07 00:00:00', 8, '11:00:00', '17:00:00', 0, 1, 2015, '2014-07-07 12:32:59'),
	(219, 'Administrator', 74, 'CYD', 3, 78, '2014-07-07 00:00:00', 8, '09:00:00', '17:00:00', 0, 1, 2015, '2014-07-07 12:32:59'),
	(221, 'Administrator', 74, 'CYD', 3, 6, '2014-07-07 00:00:00', 3, '11:00:00', '12:00:00', 0, 1, 2015, '2014-07-07 12:38:04'),
	(223, 'Administrator', 74, 'CSW', 6, 6, '2014-07-07 00:00:00', 7, '14:00:00', '16:00:00', 1, 0, 2015, '2014-07-07 14:06:39'),
	(225, 'Administrator', 74, 'CSW', 6, 35, '2014-07-07 00:00:00', 7, '09:00:00', '16:00:00', 0, 1, 2015, '2014-07-07 14:06:39'),
	(226, 'Administrator', 74, 'CSW', 6, 1, '2014-07-07 00:00:00', 7, '15:00:00', '16:00:00', 0, 1, 2015, '2014-07-07 14:06:39'),
	(231, 'Administrator', 74, 'CSW', 1, 1, '2014-07-05 00:00:00', 1, '08:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 14:49:24'),
	(232, 'Administrator', 74, 'CSW', 1, 8, '2014-07-05 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 14:50:58'),
	(233, 'Administrator', 74, 'CSW', 1, 1, '2014-07-05 00:00:00', 2, '10:00:00', '11:00:00', 0, 1, 2015, '2014-07-07 14:52:39'),
	(234, 'Administrator', 74, 'Bridges', 1, 6, '2014-07-06 00:00:00', 1, '11:00:00', '12:00:00', 1, 0, 2015, '2014-07-07 14:55:10'),
	(235, 'Administrator', 74, 'Bridges', 1, 6, '2014-07-07 00:00:00', 2, '08:00:00', '09:00:00', 1, 0, 2015, '2014-07-07 14:56:09'),
	(236, 'Administrator', 74, 'Bridges', 4, 1, '2014-07-07 00:00:00', 2, '11:00:00', '12:00:00', 0, 1, 2015, '2014-07-07 15:07:43'),
	(238, 'Administrator', 74, 'Bridges', 1, 35, '2014-07-01 00:00:00', 1, '09:00:00', '10:00:00', 1, 0, 2015, '2014-07-07 16:24:37'),
	(239, 'Administrator', 74, 'Bridges', 1, 4, '2014-07-01 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-07 16:24:43'),
	(240, 'Administrator', 74, 'CSW', 1, 4, '2014-07-08 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-08 17:05:43'),
	(242, 'Administrator', 74, 'CSW', 1, 4, '2014-07-03 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-08 19:07:08'),
	(243, 'Administrator', 74, 'CSW', 1, 4, '2014-07-03 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-08 19:07:12'),
	(244, 'Administrator', 74, 'CSW', 1, 4, '2014-07-03 00:00:00', 1, '09:00:00', '10:00:00', 0, 1, 2015, '2014-07-08 19:07:23'),
	(249, 'Administrator', 74, 'CSW', 6, 8, '2014-07-03 00:00:00', 5, '12:00:00', '17:00:00', 0, 1, 2015, '2014-07-08 23:06:11'),
	(250, 'Administrator', 74, 'CSW', 6, 8, '2014-07-03 00:00:00', 3, '17:01:00', '20:00:00', 0, 1, 2015, '2014-07-08 23:07:03'),
	(258, 'Administrator', 74, 'CSW', 4, 6, '2014-07-08 00:00:00', 1, '17:00:00', '18:00:00', 0, 1, 2015, '2014-07-08 23:16:17'),
	(259, 'Administrator', 74, 'CSW', 4, 78, '2014-07-08 00:00:00', 1, '17:00:00', '18:00:00', 0, 1, 2015, '2014-07-08 23:16:17'),
	(261, 'Administrator', 74, 'CSW', 4, 6, '2014-07-08 00:00:00', 1, '19:00:00', '20:00:00', 0, 1, 2015, '2014-07-08 23:16:34'),
	(262, 'Administrator', 74, 'CSW', 4, 78, '2014-07-08 00:00:00', 1, '19:00:00', '20:00:00', 0, 1, 2015, '2014-07-08 23:16:34'),
	(264, 'Administrator', 74, 'Bridges', 3, 6, '2014-07-08 00:00:00', 1, '21:00:00', '22:00:00', 0, 1, 2015, '2014-07-08 23:18:09'),
	(265, 'Administrator', 74, 'Bridges', 3, 78, '2014-07-08 00:00:00', 1, '21:00:00', '22:00:00', 0, 1, 2015, '2014-07-08 23:18:09')
    ;
    

Open in new window

0
 

Author Comment

by:al4629740
ID: 40387567
VERY GOOD
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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