An SQL query to intersect datetime intervals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

An SQL query to intersect datetime intervals

Author

Topic

Istrebitel
Starting Member

2 Posts

Posted - 2010-04-29 : 04:21:16

Greetings!

(i suppose this long explanation can be skipped to the bottom of the post where i sum up my question)

I need to write a query for the report (using Reporting Services but that doesnt matter). The report should compare the datetime intervals from different tables. Here is the example of what i have. This is a system that provides reports on the employees of the company and their presence on the work (or absence).

1) I have Table Presence - it contains rows of data about when the employee entered and exited the building.

------------------------------------------
ID | Person_ID | Date | Time_In | Time_Out
------------------------------------------

so for example typical rows for a typical employee would look like:

1 1 29.04.2010 10:00 13:00
2 1 29.04.2010 14:00 19:00

That means that at the day of 29 april 2010 the person with id 1 came to work at 10:00, then went to lunch from 13:00 till 14:00 and went home 19:00

Now for example i can do "Sum(Time_Out-Time_In)" and have total time in seconds the person was present at the work.

2) I also have data (imported via XML from sharepoint server) about when the person informed his boss he will be absent (our employees have to fill a form like "i will be absent from XX:XX till XX:XX" and the reason is provided, business or personal). We call it "business assignment". Basically this data can be put into a table like:

----------------------------------------------------
ID | Person_ID | Date | Time_From | Time_Till | Kind
----------------------------------------------------

So, now in my report, i need to show among other things the total time an employee was at work each day. That would be easilly done with Sum(Time_Out-Time_In) over table Presence, but now i need to take those business assignments into account. So, for example, if the person was on the work from 10:00 till 13:00, then from 14:00 till 15:30 and then from 17:00 till 19:00, but he had a business assignment from 15:00 till 17:00 to consult a client outside the work building, the report should show that he worked a total of 8 hours that day.
The report should intersect the intervals 14:00 - 15:30 and 17:00 - 19:00 with 15:00 - 17:00 and yeild the total 5 hours which is the sum of the intervals.

Now, i dont quite understand how is this done with TSQL. It should be (and i suppose it is doable) but i cant get it how. I know about recursive queries, multiple queries, temporarily tables and i've done complex reports that transform data in numerous ways but here i need your help. Any hints would be appreciated.

--------------------------------------------------

To summ it up, i have two tables of intervals in time that look like

-------------------------------
ID | Date | Time_From | Time_To
-------------------------------

Ex.:

Table 1

1 29.04.2010 10:00 13:00
2 29.04.2010 14:00 15:30
3 29.04.2010 17:00 19:00

Table 2

1 29.04.2010 15:00 17:00

and i need to get a query that returns a result like

--------------------
Date | Total_Seconds
--------------------

Ex.:

29.04.2010 28800

Where Total_Seconds is the summ of intersection of intervals (intervals 14:00 - 15:30, 17:00 - 19:00 intersect with 15:00 - 17:00 to form a total of 5 hours, and thats the number i want to get in my query, while their total sum of those intervals would be 5 hours 30 minutes, thats easilly acquireable but not what i want to get)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-29 : 04:49:03

The below example should give you an idea on how to start with..

Declare @WorkTable Table
(Id int ,
WDate Date,
TimeFrom Time,
TimeTo Time
)

Declare @MeetingTable Table
(Id int ,
WDate Date,
TimeFrom Time,
TimeTo Time
)

Insert into @WorkTable
Values (1,'2010-04-01', '10:10','12:10'), (1,'2010-04-01', '13:10','18:10'),(2,'2010-04-01', '10:00','12:10'),
(1,'2010-04-02', '10:15','12:00')

Insert into @MeetingTable
Values (1,'2010-04-01', '12:15','13:00'), (2,'2010-04-01', '13:10','18:10')

Select Id,WDate,SUM(SecondDiff) as SecondDiff from
(
Select ID, Wdate, DATEDIFF(ss, TimeFrom,TimeTo ) SecondDiff from @WorkTable
Union All
Select ID,Wdate ,DATEDIFF(ss, TimeFrom,TimeTo ) SecondDiff from @MeetingTable
) as SubTab
Group by Id,WDate

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Istrebitel
Starting Member

2 Posts

Posted - 2010-04-29 : 07:35:37

Yes but this basically sums the intervals, it does not provide the sums of intersections of the intervals. thats what i was talking about. Getting a summ of all intervals is simple (i.e. 10:00-13:30 + 12:00-14:00 would be 5 hours 30 minutes) but i need their intersection (i.e. 10:00-13:00 + 12:00-14:00 would be 4 hours)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-29 : 09:08:20

quote:
Originally posted by Istrebitel

Yes but this basically sums the intervals, it does not provide the sums of intersections of the intervals. thats what i was talking about. Getting a summ of all intervals is simple (i.e. 10:00-13:30 + 12:00-14:00 would be 5 hours 30 minutes) but i need their intersection (i.e. 10:00-13:00 + 12:00-14:00 would be 4 hours)

Hi Istrebitel,

I am little bit confused on how you are calculating intersection between two times (i.e. 10:00-13:00 + 12:00-14:00 would be 4 hours - How ?).

Can you give more examples for intersection.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 09:33:00

quote:
Originally posted by pk_bohra
Hi Istrebitel,

I am little bit confused on how you are calculating intersection between two times (i.e. 10:00-13:00 + 12:00-14:00 would be 4 hours - How ?).

Can you give more examples for intersection.

It's pretty simple really. If someone was in the office between 10:00 and 13:00, but has a "business assignment" record from 12:00 to 14:00 on the same day, then they have worked only 4 hours. But if you sum the difference of these 2 time intervals, it will return 5 hours, as they overlap.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-30 : 00:23:26

For 2008:

Declare @WorkTable Table
(Id int ,
WDate Date,
TimeFrom Time,
TimeTo Time
)

Declare @MeetingTable Table
(Id int ,
WDate Date,
TimeFrom Time,
TimeTo Time
)

Insert into @WorkTable
Select 1,'2010-04-01', '10:10','13:10' union
Select 1,'2010-04-01', '15:10','18:10' union
Select 2,'2010-04-01', '10:00','14:10' union
Select 1,'2010-04-02', '10:15','12:00'

Insert into @MeetingTable
Select 1,'2010-04-01', '12:15','14:00' Union
Select 2,'2010-04-01', '13:10','18:10'

Declare @DataTable Table
(
Srno int identity,
Id int,
WDate datetime,
TimeFrom Datetime,
TimeTo datetime)

Insert into @DataTable
Select * From (
Select ID, Wdate, TimeFrom , TimeTo from @WorkTable
Union All
Select ID,Wdate , TimeFrom , TimeTo from @MeetingTable
) as SubTab
Order by Id,WDate,TimeFrom

Select T.Id, T.Wdate, Sum(SecDiff) As DiffInSec
From
(
SeLect T1.Srno,T1.Id,T1.WDate,
Datediff(ss,T1.TimeFrom,
Case When T1.WDate = T2.Wdate and T1.TimeTo > T2.TimeFrom and T2.TimeFrom is not null Then T2.TimeFrom
When T2.TimeFrom is null Then T1.TimeTo
Else
T1.TimeTo
End ) As SecDiff
From @DataTable T1 left join @DataTable T2
on T1.Srno + 1 = T2.Srno
) As T
Group by T.Id, T.Wdate

I have not carried out any through testing. In case you are getting wrong output then let me know of the same.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources