Date Overlap - SQL Server Forums

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

New to SQL Server Programming

Date Overlap

Author

Topic

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-04-17 : 20:47:31

Hi

Say I've got two time intervals represented two rows of Start Date and End Date.

Can anyone think of a way to express a query in which I can work out how many days the time intervals overlap each otheR?

Like for example if the first interval is pretty 1/5/08 and 13/5/08
and the second one is 5/5/08 and 15/5/08, then i guess the answer should be 8 days.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-18 : 00:56:11

You need to give the table structure if you want to get a query that will work with the table, but here is the logic that you can use to get the interval, if you use an expression like this with a self join on the table to get the rows you want to compare, then you should be able to write the query against the table. This assumes that the start date is <= the end date in any given row, but it does allows for no overlap, in which case it returns 0.

DECLARE @StartDate1 datetime, @EndDate1 datetime
    , @StartDate2 datetime, @EndDate2 datetime
SELECT @StartDate1 = '1/5/08', @EndDate1 = '13/5/08'
    , @StartDate2 = '5/5/08', @EndDate2 = '15/5/08'
SELECT CASE 
    WHEN @EndDate1 < @StartDate2 OR @EndDate2 < @StartDate1 
    THEN 0
    WHEN @StartDate1 <= @StartDate2 AND @EndDate1 <= @EndDate2 
    THEN datediff(day, @StartDate2, @EndDate1)
    WHEN @StartDate1 <= @StartDate2 AND @EndDate1 >= @EndDate2 
    THEN datediff(day, @StartDate2, @EndDate2)
    WHEN @StartDate1 >= @StartDate2 AND @EndDate1 <= @EndDate2 
    THEN datediff(day, @StartDate1, @EndDate1)
    WHEN @StartDate1 >= @StartDate2 AND @EndDate1 >= @EndDate2 
    THEN datediff(day, @StartDate1, @EndDate2)
    END AS [Interval]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 01:16:44

See this post. look for Pesos solution. I think you can modify it to fit your requiorement:-

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

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-04-18 : 02:57:01

i ended up doing this and it worked pretty well... i was just hoping there'd be an easier way that didn't involve a function, cause i do sum it all up in a select statement and that might affect performance.

ALTER FUNCTION [dbo].[fn_GetOverlappingPlacementDays]
-- TC = training contract, P = placement, SD = startDate, ED = endDate
(@TC_SD datetime, @TC_ED datetime, @P_SD datetime, @P_ED datetime)
RETURNS int
AS
BEGIN
Declare @ReturnValue int

if( @TC_ED <= @P_SD)
return 0

if( @P_ED <= @TC_SD)
return 0

if( @TC_SD <= @P_ED and @P_SD <= @TC_ED and @P_ED >= @TC_ED)
return abs( datediff(d, @TC_ED , @P_SD) )

if( @TC_SD >= @P_SD and @TC_SD <= @P_ED and @TC_ED >= @P_ED)
return abs( datediff(d, @P_ED , @TC_SD))

If( @TC_SD <= @P_SD and @TC_ED >= @P_ED)
return abs( datediff(d, @P_ED , @P_SD))

if( @TC_SD > @P_SD and @TC_ED < @P_ED)
return abs( datediff(d, @TC_ED , @TC_SD))

Return 0
END

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-18 : 05:18:12

This would be my effort. Using this same technique directly (i.e. without a function) should improve performance.

ALTER FUNCTION [dbo].[fn_GetOverlappingPlacementDays]
-- TC = training contract, P = placement, SD = startDate, ED = endDate
(@TC_SD datetime, @TC_ED datetime, @P_SD datetime, @P_ED datetime)
RETURNS int
AS
/*
Assumptions: @TC_SD < @TC_ED; @P_SD < @P_ED; no time element in any dates

Examples:
select dbo.fn_GetOverlappingPlacementDays('20080501', '20080513', '20080505', '20080515') --8
select dbo.fn_GetOverlappingPlacementDays('20080501', '20080505', '20080505', '20080515') --0
select dbo.fn_GetOverlappingPlacementDays('20080501', '20080503', '20080505', '20080515') --0
select dbo.fn_GetOverlappingPlacementDays('20080506', '20080508', '20080505', '20080515') --2
select dbo.fn_GetOverlappingPlacementDays('20080518', '20080522', '20080505', '20080515') --0
select dbo.fn_GetOverlappingPlacementDays('20080514', '20080522', '20080505', '20080515') --1
select dbo.fn_GetOverlappingPlacementDays('20080515', '20080522', '20080505', '20080515') --0
select dbo.fn_GetOverlappingPlacementDays('20080515', '20080515', '20080505', '20080515') --0
*/
BEGIN
Declare @ReturnValue int

select @ReturnValue = case
    when @P_ED <= @TC_SD or @TC_ED <= @P_SD then 0
    else
        datediff(day,
            case when @TC_SD < @P_SD then @P_SD else @TC_SD end,
            case when @TC_ED < @P_ED then @TC_ED else @P_ED end)
    end
Return @ReturnValue
END

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-20 : 00:43:21

quote:
Originally posted by liptonIcedTea

i ended up doing this and it worked pretty well... i was just hoping there'd be an easier way that didn't involve a function, cause i do sum it all up in a select statement and that might affect performance.
...

The reason that you had to create a function is that you are using IF instead of the CASE as RyanRandall and I both showed you. If you use CASE you can do it right in your SELECT without a function. Having said that a function should perform OK because you are not doing any data access in the function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources