SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Time overlap calculations
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/06/2007 :  10:15:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There has been a number of topics recently regarding calculations of overlapping times. Here is one approach to reach this with a UDF.
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


Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 01/07/2007 :  18:16:35  Show Profile  Reply with Quote
Here is the same using 2005 CTE.
Just for fun really...

rockmoose

create function dbo.fntimeoverlap_2005
(
	@FromTime datetime,
	@ToTime datetime,
	@Login datetime,
	@Logout datetime
)
returns int
as
begin
	declare @seconds int
	
	;with t(t0,t1) as
	(
		select min(t),max(t) from
		(select @FromTime union all select @ToTime)s(t)
	)
	,s(s0,s1) as
	(
		select min(t),max(t) from
		(select @Login union all select @Logout)s(t)
	)
	select @seconds = datediff(	second
					,(select max(t) from(select t0 from t union all select s0 from s)x(t))
					,(select min(t) from(select t1 from t union all select s1 from s)y(t))	)
	where	(select s0 from s) <= (select t1 from t)
		and
		(select s1 from s) >= (select t0 from t)

	return @seconds
end
go
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 01/14/2008 :  06:51:56  Show Profile  Visit henrikop's Homepage  Reply with Quote
I'd like to make a (CLR) Function which has as input a Begin and End date(time) and a datatable with a set of begin-end dates, and a return boolean which returns TRUE if there's an overlap and FALSE if there isn't.

Do you have some directions? I'd will save me a lot of time making one myself.



Henri
~~~~
There's no place like 127.0.0.1
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/14/2008 :  07:00:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If there is an overlap?

IF @Range1Start <= @Range2End AND @Range1End >= @Range2Start
PRINT 'Overlap'
ELSE
PRINT 'No overlap'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 01/14/2008 :  08:07:00  Show Profile  Visit henrikop's Homepage  Reply with Quote
Ehh, maybe I put it down too simple.

I have an application with a lot of date-time things. Because it has to do with different tables I don't want to write similar functions again and again.

A record can have a begin and enddate, or one of them , or none. I'd like to add a record, but it's begin and enddate may nog overlap with existing begin-end records.

usualy I can use constructs like

INSERT INTO SomeTable (ID, InstanceId, Begin, End) SELECT @ID, @InstanceId, @Begin, @end)
WHERE NOT EXISTS (SELECT * FROM SomeTable st WHERE InstanceId = @InstanceId AND
st.Begin > @BeginDate OR @BeginDate IS NULL..... and so on

to make sure a record has nog overlap with an existing record based on same instanceid.

But because of the many times I have to use it I'd like to make a function which can check of @Begin and @End would create an overlap on existing records.

Did I clarify my question, or should I be more specific? and create some real examples. (I don't want to consume your time, but you might know where I can find the script)


Henri
~~~~
There's no place like 127.0.0.1
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/14/2008 :  08:15:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use COALESCE(@FromDate, '17530101')
and COALESCE(@ToDate, '99991231')

for dateranges containing NULLs and where NULL means "forever" for enddate and "until now" for startdate.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000