| Author |
 Topic  |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
 Posted - 01/06/2007 : 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
Sweden
3279 Posts |
Posted - 01/07/2007 : 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 |
 |
|
|
henrikop
Constraint Violating Yak Guru
Netherlands
273 Posts |
Posted - 01/14/2008 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 01/14/2008 : 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" |
|
|
|
henrikop
Constraint Violating Yak Guru
Netherlands
273 Posts |
Posted - 01/14/2008 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 01/14/2008 : 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" |
|
|
| |
Topic |
|
|
|
Time overlap calculations
