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
 General SQL Server Forums
 Script Library
 Time overlap calculations

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-06 : 10:15:47
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

3279 Posts

Posted - 2007-01-07 : 18:16:35
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

280 Posts

Posted - 2008-01-14 : 06:51:56
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

30421 Posts

Posted - 2008-01-14 : 07:00:07
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

280 Posts

Posted - 2008-01-14 : 08:07:00
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

30421 Posts

Posted - 2008-01-14 : 08:15:22
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

tengtium
Starting Member

5 Posts

Posted - 2015-12-11 : 09:59:07
this is great. the udf is perfect in computing regular hours within the schedule.

what i'm trying to figure out is the time interval outside the @FromTime and @ToTime.

assuming i have this following info.
@FromTime : 2015-07-07 01:00:00.000
@ToTime : 2015-07-07 10:00:00.000

those represents the schedule.

below are the login in and out
@Login : 2015-07-07 00:00:00.000
@Logout : 2015-07-07 11:00:00.000

im trying to figure out a udf that will return the nonoverlapping time as overtime.

in the given data above. the time return will be 2 hours. that is 1 hour from @Login : 2015-07-07 00:00:00.000 to @FromTime : 2015-07-07 01:00:00.000 and 1 hour from @ToTime : 2015-07-07 10:00:00.000 to @Logout : 2015-07-07 11:00:00.000

please help.




Go to Top of Page
   

- Advertisement -