Solved

Find overlapping times in sql

Posted on 2014-10-12
10
103 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 45
Run SQL Server Proc from Access 11 31
Deal with apostrophe in stored procedures 8 43
SQL server vNext 18 29
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 …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

803 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