Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Find overlapping times in sql

Posted on 2014-10-12
10
Medium Priority
?
148 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
  • 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 49

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
Industry Leaders: 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!

 
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 49

Accepted Solution

by:
PortletPaul earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in 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.

916 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