Solved

Find overlapping times in sql

Posted on 2014-10-12
10
89 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 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 40

Expert Comment

by:Sharath
ID: 40376378
Provide some sample data and expected result.
0
 
LVL 142

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

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 31

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server RDS clr assembly 4 36
Slow SQL query 12 21
Access Migration to Sql Server 2 21
SQL JOIN + SUBQUERY? 3 13
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

19 Experts available now in Live!

Get 1:1 Help Now