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
Solved

Find overlapping times in sql

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

860 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