Overlapping date duration

[graabein]

Hi, I have a list of events that I want to filter on certain days and time of day. My events are in a table called events with event_start and event_end datetimes.

My proc goes something like this:

create table #days (
  from datetime, 
  to datetime)

insert #days (
  from, 
  to
) values (
  '2008-11-22 00:00', 
  '2008-11-22 06:00')

insert #days (
  from, 
  to
) values (
  '2008-11-23 00:00',
  '2008-11-23 06:00')

--...
--drop table #days

Say I have an event from 2008-11-21 23:15 to 2008-11-22 01:30 and another event from 2008-11-23 00:00 to 2008-11-23 00:30. The overlapping time is a total of 2 hours.

How do I go about to calculate this?


graabein

 

[graabein]

Solved it myself. It was easy really.

I used this function (just with minutes instead):

CREATE FUNCTION dbo.fnTimeOverlap
(
	@FromTime DATETIME,
	@ToTime DATETIME,
	@Login DATETIME,
	@Logout DATETIME
)
RETURNS INT
AS

BEGIN
	DECLARE	@Temp DATETIME,
		@Seconds INT

	IF @FromTime > @ToTime
		SELECT	@Temp = @FromTime,
			@FromTime = @ToTime,
			@ToTime = @Temp

	IF @Login > @Logout
		SELECT	@Temp = @Login,
			@Login = @Logout,
			@Logout = @Temp

	SELECT	@Seconds = 	CASE
					WHEN @FromTime <= @Login AND @Login <= @ToTime AND @ToTime <= @Logout THEN DATEDIFF(second, @Login, @ToTime)
 					WHEN @FromTime <= @Login AND @Logout <= @ToTime THEN DATEDIFF(second, @Login, @Logout)
					WHEN @Login <= @FromTime AND @ToTime <= @Logout THEN DATEDIFF(second, @FromTime, @ToTime)
					WHEN @Login <= @FromTime AND @FromTime <= @Logout AND @Logout <= @ToTime THEN DATEDIFF(second, @FromTime, @Logout)
				END

	RETURN	@Seconds
END

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77202

Then I did a simple query like this:

create table #t (from datetime, to datetime)
create table #e (start datetime, end datetime)

insert #t (from, to) values ('2008-11-22 00:00', '2008-11-22 02:00')
insert #t (from, to) values ('2008-11-23 00:00', '2008-11-23 02:00')
insert #t (from, to) values ('2008-11-24 00:00', '2008-11-24 02:00')

insert #e (start, end) values ('2008-11-21 23:15', '2008-11-22 01:30')
insert #e (start, end) values ('2008-11-22 01:00', '2008-11-22 01:30')

select 
	[duration] = sum(dbo.udf_FindMinutes(#e.start, #e.end, #t.from, #t.to))
from
	#e, #t

drop table #t
drop table #e

graabein