Overlapping times

Question

I Have data as follows:(assuming on a single day,this is sample data)

date                studentID    computer   starttime     endtime

04/05/2012     a                 1              3:00pm      6:00pm

04/05/2012     a                 2              4:00pm      5:00pm

04/05/2012    b                 3              3:00pm      6:00pm

04/05/2012    b                 4             6:00pm      7:00pm

04/05/2012    b                 4              02:30pm     5:30pm

I want to output all the records that have overlapping time period for a particular student.The result output will be

date                studentID    computer   starttime     endtime

04/05/2012      a                 1              3:00pm     6:00pm

04/05/2012     a                 2              4:00pm      5:00pm

04/05/2012     b                 3              3:00pm      6:00pm

04/05/2012     b                 4              02:30pm     5:30pm

Thanks for your time and patience.

Answer 1

The two-record case is simple; just self join and cross or cross apply.  Use of the EXISTS clause is probably a better option.  For example:

declare @test table( theDate dateTime, studentId varchar(10), 
  computer int, startTime datetime, endTime datetime);
insert into @test
select '04/05/2012', 'a', 1, '15:00', '18:00' union all
select '04/05/2012', 'a', 2, '16:00', '17:00' union all
select '04/05/2012', 'b', 3, '15:00', '18:00' union all
select '04/05/2012', 'b', 4, '18:00', '19:00' union all
select '04/05/2012', 'b', 4, '14:30', '17:30'
;
select *
from @test a
where exists
( select 0 from @test b
  where b.studentId =  a.studentId
    and b.computer <> a.computer
    and ( b.StartTime <= a.StartTime  and  b.endTime > a.startTime  or
          a.startTime <= b.startTIme  and  a.endTime > b.startTime
        )
)
;
/* -------- Ouptut: --------
theDate                 studentId  computer    startTime               endTime
----------------------- ---------- ----------- ----------------------- -----------------------
2012-04-05 00:00:00.000 a          1           1900-01-01 15:00:00.000 1900-01-01 18:00:00.000
2012-04-05 00:00:00.000 a          2           1900-01-01 16:00:00.000 1900-01-01 17:00:00.000
2012-04-05 00:00:00.000 b          3           1900-01-01 15:00:00.000 1900-01-01 18:00:00.000
2012-04-05 00:00:00.000 b          4           1900-01-01 14:30:00.000 1900-01-01 17:30:00.000
*/

Answer 2

You need a PK value for this to work.  Seems like there would be an easier way to do this - but here is what I came up with

create table #times(PKid int, day date, student char(1), computer int, starttime time, endtime time)

INSERT #times VALUES(1,'04/05/2012'     ,'a' ,                1 ,             '3:00pm'  ,    '6:00pm')
 INSERT #times VALUES(2,'04/05/2012'    , 'a' ,                2 ,             '4:00pm' ,     '5:00pm')
 INSERT #times VALUES(3,'04/05/2012'    ,'b'   ,              3  ,            '3:00pm'  ,    '6:00pm')
 INSERT #times VALUES(4,'04/05/2012'    ,'b'    ,             4  ,           '6:00pm'   ,   '7:00pm')
 INSERT #times VALUES(5,'04/05/2012'    ,'b'     ,            4  ,            '02:30pm' ,    '5:30pm')

 select Distinct t1.* 
 FROM #times t1
 JOIN #times t2 on t1.day = t2.day 
	and t1.student = t2.student 
	AND t1.PKid != t2.PKid
	and  ((t1.starttime > t2.starttime and t1.starttime < t2.endtime) 
		or (t1.endtime > t2.starttime and t1.endtime < t2.endtime)
		or (t1.starttime < t2.starttime and  t1.endtime> t2.endtime))

---

Chuck

Answer 3

Overlapping time?

Why you are not taking in mind the three rows for studenid = 'b'?

All those times are overlapping, unless you have your own definition for overlapping.

If the intervals overlapping are from different computer, then try:

SET NOCOUNT ON;
USE tempdb;
GO
DECLARE @T TABLE (
date_col date,
studentID char(1),
computer int,
starttime datetime,
endtime datetime
);

INSERT INTO @T (date_col, studentID, computer, starttime, endtime)
VALUES
('04/05/2012', 'a', 1, '3:00pm', '6:00pm'),
('04/05/2012', 'a', 2, '4:00pm', '5:00pm'),
('04/05/2012', 'b', 3, '3:00pm', '6:00pm'),
('04/05/2012', 'b', 4, '6:00pm', '7:00pm'),
('04/05/2012', 'b', 4, '02:30pm', '5:30pm');

SELECT
	*
FROM
	@T AS A
WHERE
	EXISTS (
	SELECT
		*
	FROM
		@T AS B
	WHERE
		B.studentID = A.studentID
		AND B.computer <> A.computer
		AND B.starttime < A.endtime
		AND B.endtime > A.starttime  
	);
GO

/*

date_col	studentID	computer	starttime	endtime
2012-04-05	a	1	1900-01-01 15:00:00.000	1900-01-01 18:00:00.000
2012-04-05	a	2	1900-01-01 16:00:00.000	1900-01-01 17:00:00.000
2012-04-05	b	3	1900-01-01 15:00:00.000	1900-01-01 18:00:00.000
2012-04-05	b	4	1900-01-01 14:30:00.000	1900-01-01 17:30:00.000

*/

---

AMB

Some guidelines for posting questions...

Answer 4

Overlapping time?

Why you are not taking in mind the three rows for studenid = 'b'?

All those times are overlapping, unless you have your own definition for overlapping.

---

AMB

Some guidelines for posting questions...

He seems to not count a start and end that occur at the same time as an overlap. (One ended at 6:00pm and another started at 6:00pm) Which is why I used the <> logic rather than a between

---

Chuck

Answer 5

Thanks, Chuck!

---

AMB

Some guidelines for posting questions...

Answer 6

Sorry to not get my question right.Yes the result set I need to find out is,Students using different computers concurrently i.e. in a same period of time if he uses two computers I want to list those records.

Answer 7

Try

declare @StudentSchedule table (dt date,                studentID  varchar(10),  computer int,   starttime  time,   endtime time)

insert into @StudentSchedule
select
'04/05/2012',     'a',                 1,              '3:00 pm',      '6:00 pm'
union all select

'04/05/2012',     'a',                 2,              '4:00pm',      '5:00pm'
union all select
'04/05/2012',    'b',                 3,              '3:00pm',      '6:00pm'
union all select
'04/05/2012',    'b',                 4,             '6:00pm',      '7:00pm'
union all select
'04/05/2012',    'b',                 4,              '02:30pm',     '5:30pm'

select * from @StudentSchedule S where exists (select 1 from @StudentSchedule S1
where S1.studentID = S.studentID and S1.computer <> S.computer and ((S1.starttime < S.Endtime and S1.EndTime > S.Starttime) 
OR (S.Starttime < S1.Endtime and S.endtime > S1.Starttime))) 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

select * from @StudentSchedule S where exists (select 1 from @StudentSchedule S1
where S1.studentID = S.studentID and S1.computer <> S.computer and ((S1.starttime < S.Endtime and S1.EndTime > S.Starttime) 
OR (S.Starttime < S1.Endtime and S.endtime > S1.Starttime))) 

In my previous response I preferred the EXISTS semi-join over the self join because I saw the need for the DISTINCT clause when doing the self join; this would precipitate a sort and have a harmful affect on performance.

Answer 9

Thanks chuck.It worked to an extent.Except I dont have a Pk.So I used the time constraints in your code

Answer 10

Please post DDL, so that people do no have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you did not). Temporal data should use ISO-8601 formats (you did not). Code should be in Standard SQL as much as possible and no local dialect. DATE is a reserved word, etc. This is minimal polite behavior on a SQL forum. 

What you did post is fundamentally wrong. You have a design flaw called attribute spiting. Download a free copy of he Rick Snodgrass book on temporal SQL. Learn how time works and learn he definition of ANSI Standard OVERLAPS() predicate. Time is a continuum and has o be modeled as half-open durations – that means (start_timestamp, end_timestamp) pairs.

You also confused rows and records, which leads o his kind of mindset screw ups.

CREATE TABLE Computer_Signups

(student_id CHAR(1) NOT NULL,

REFERENCES Students (student_id),

computer_nbr INTEGER NOT NULL

CHECK (computer_nbr BETWEEN 1 AND 10),

signin_timestamp DATETIME2(0) NOT NULL,

signout_timestamp DATETIME2(0),

CHECK (signin_timestamp < signout_timestamp),

PRIMARY KEY (student_id, computer_nbr, signin_timestamp));

INSERT INTO Computer_Signups

VALUES

('Alpha', 1, '2012-04-05 15:00:00', '2012-04-05 18:00:00'),

('Alpha', 2, '2012-04-05 16:00:00', '2012-04-05 19:00:00'),

('Alpha', 3, '2012-04-05 15:00:00', '2012-04-05 18:00:00'),

('Beta', 4, '2012-04-05 18:00:00', '2012-04-05 21:00:00'),

('Beta', 4, '2012-04-05 14:30:00', '2012-04-05 17:30:00');

Do you want to try it now?

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 11

Thank you for us up on this Joe; good points.

Answer 12

Hi,

I am trying to calculate the overlapping time period in minutes for the same data and query above.How can I do that?For the data above I wanted to see the result set as .

date                studentID    computer   starttime     endtime   overlapping time

04/05/2012      a                 1              3:00pm      6:00pm      60

04/05/2012     a                 2              4:00pm      5:00pm       60

04/05/2012     b                 3              3:00pm      6:00pm       150

04/05/2012     b                 4              02:30pm     5:30pm       150

Thanks,