Time Overlap Calculations

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