Overlap of Two Date Windows

originally posted 2014-05-14

1	originally posted 2014-05-14

s1 is the start of Window 1
e1 is the end of Window 1
s2 is the start of Window 2
e2 is the end of Window 2
s1 < e1
s2 < e2
Window 1 Total Days = (e1 – s1) + 1
>Window 2 Total Days = (e2 – s2) + 1

create function [dbo].[SIP_NPT_fGetDateWindowsOverlap](@s1 date,@e1 date,@s2 date, @e2 date)
returns int
as
begin
  declare @overlap int = null;

  if(@s1 is null or @e1 is null or @s2 is null or @e2 is null) return @overlap;

  declare @swap date
  if(@s1>@e1) 
  begin 
    set @swap = null
    set @swap = @e1
    set @e1 = @s1
    set @s1 = @swap
  end

  if(@s2>@e2) 
  begin
    set @swap = null
    set @swap = @e2
    set @e2 = @s2
    set @s2 = @swap
  end

  select
  @overlap = convert(int,
    case when not (@s1>@e2 or @e1<@s2) then
      case when @s1=@e2 or @e1=@s2 then 1 --overlap of one day
      when @s1>@s2 and @e1>@e2 then abs(datediff(d,@e2,@s1)) + 1 --partial overlap
      when @s1<@s2 and @e1<@e2 then abs(datediff(d,@e1,@s2)) + 1 --partial overlap
      when (@s1>@s2 and @e1<@e2) or (@s1>@s2 and @e1=@e2) or (@s1=@s2 and @e1<@e2) then datediff(d,@s1,@e1) + 1 --overlap by total days in window 1
      when (@s1<@s2 and @e1>@e2) or (@s1<@s2 and @e1=@e2) or (@s1=@s2 and @e1=@e2) or (@s1=@s2 and @e1>@e2) then datediff(d,@s2,@e2) + 1 --overlap by total days in window 2
      end
    else 0 --no overlap
    end)

  return @overlap
end
GO

create function [SIP_NPT_tfGetDateWindowsOverlap](@s1 date,@e1 date,@s2 date, @e2 date)
returns @tab table 
(
[S1] date
,[E1] date
,[S2] date
,[E2] date
,[W1Total] int
,[W2Total] int
,[Overlap] int
--,[Method] nvarchar(100)
)
as
begin
  insert @tab
  select
  [S1] = @s1
  ,[E1] = @e1
  ,[S2] = @s2
  ,[E2] = @e2
  ,[W1Total] = datediff(d,@s1,@e1) + 1
  ,[W2Total] = datediff(d,@s2,@e2) + 1
  ,[Overlap] = [dbo].[SIP_NPT_fGetDateWindowsOverlap](@s1,@e1,@s2,@e2)
  return;
end
GO

declare @tab table
(
  [Scenario] int
  ,[S1] date
  ,[E1] date
  ,[S2] date
  ,[E2] date
  ,[W1Total] int
  ,[W2Total] int
  ,[Overlap] int
  --,[Method] nvarchar(100)
)

insert @tab select [Scenario] = 1, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/1/2014','1/10/2014')
insert @tab select [Scenario] = 2, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/15/2014')
insert @tab select [Scenario] = 3, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/18/2014')
insert @tab select [Scenario] = 4, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/18/2014')
insert @tab select [Scenario] = 5, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/17/2014','1/19/2014')
insert @tab select [Scenario] = 6, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/20/2014')
insert @tab select [Scenario] = 7, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/18/2014','1/20/2014')
insert @tab select [Scenario] = 8, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/25/2014')
insert @tab select [Scenario] = 9, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/25/2014')
insert @tab select [Scenario] = 10, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/20/2014')
insert @tab select [Scenario] = 11, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/18/2014','1/25/2014')
insert @tab select [Scenario] = 12, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/20/2014','1/25/2014')
insert @tab select [Scenario] = 13, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/25/2014','1/30/2014')

select * from @tab order by [Scenario

create function [dbo].[SIP_NPT_fGetDateWindowsOverlap](@s1 date,@e1 date,@s2 date, @e2 date)

returns int

begin

declare @overlap int = null;

if(@s1 is null or @e1 is null or @s2 is null or @e2 is null) return @overlap;

declare @swap date

if(@s1>@e1)

begin

set @swap = null

set @swap = @e1

set @e1 = @s1

set @s1 = @swap

end

if(@s2>@e2)

begin

set @swap = null

set @swap = @e2

set @e2 = @s2

set @s2 = @swap

end

select

@overlap = convert(int,

case when not (@s1>@e2 or @e1<@s2) then

case when @s1=@e2 or @e1=@s2 then 1 --overlap of one day

when @s1>@s2 and @e1>@e2 then abs(datediff(d,@e2,@s1)) + 1 --partial overlap

when @s1<@s2 and @e1<@e2 then abs(datediff(d,@e1,@s2)) + 1 --partial overlap

when (@s1>@s2 and @e1<@e2) or (@s1>@s2 and @e1=@e2) or (@s1=@s2 and @e1<@e2) then datediff(d,@s1,@e1) + 1 --overlap by total days in window 1

when (@s1<@s2 and @e1>@e2) or (@s1<@s2 and @e1=@e2) or (@s1=@s2 and @e1=@e2) or (@s1=@s2 and @e1>@e2) then datediff(d,@s2,@e2) + 1 --overlap by total days in window 2

end

else 0 --no overlap

end)

return @overlap

end

create function [SIP_NPT_tfGetDateWindowsOverlap](@s1 date,@e1 date,@s2 date, @e2 date)

returns @tab table

(

[S1] date

,[E1] date

,[S2] date

,[E2] date

,[W1Total] int

,[W2Total] int

,[Overlap] int

--,[Method] nvarchar(100)

)

begin

insert @tab

select

[S1] = @s1

,[E1] = @e1

,[S2] = @s2

,[E2] = @e2

,[W1Total] = datediff(d,@s1,@e1) + 1

,[W2Total] = datediff(d,@s2,@e2) + 1

,[Overlap] = [dbo].[SIP_NPT_fGetDateWindowsOverlap](@s1,@e1,@s2,@e2)

return;

end

declare @tab table

(

[Scenario] int

,[S1] date

,[E1] date

,[S2] date

,[E2] date

,[W1Total] int

,[W2Total] int

,[Overlap] int

--,[Method] nvarchar(100)

)

insert @tab select [Scenario] = 1, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/1/2014','1/10/2014')

insert @tab select [Scenario] = 2, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/15/2014')

insert @tab select [Scenario] = 3, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/18/2014')

insert @tab select [Scenario] = 4, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/18/2014')

insert @tab select [Scenario] = 5, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/17/2014','1/19/2014')

insert @tab select [Scenario] = 6, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/20/2014')

insert @tab select [Scenario] = 7, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/18/2014','1/20/2014')

insert @tab select [Scenario] = 8, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/25/2014')

insert @tab select [Scenario] = 9, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/25/2014')

insert @tab select [Scenario] = 10, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/20/2014')

insert @tab select [Scenario] = 11, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/18/2014','1/25/2014')

insert @tab select [Scenario] = 12, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/20/2014','1/25/2014')

insert @tab select [Scenario] = 13, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/25/2014','1/30/2014')

select * from @tab order by [Scenario

Example Usage:

--DECLARE A TEMP TABLE
create table #temp
(
  [EmployeeId] nvarchar(100)
  ,[ProjectId] nvarchar(100)
  ,[ProjectStart] date
  ,[ProjectEnd] date
)

--FILL TEMP TABLE WITH DATA HERE
...

--DETERMINE THE OVERLAP FOR EACH EMPLOYEE PROJECT TO EVERY OTHER EMPLOYEE PROJECT
select * 
,[PercentOverlap] = 
  case when [TotalProjectDays] <> 0 and [OverlapDays] is not null then ([OverlapDays] / convert(decimal(18,2),[TotalProjectDays])) * 100
  else 0
  end
from
(
  select  
  EmployeeProject.*
  ,[OtherProjectId] = otherproject.[ProjectId]
  ,[OtherProjectStart] = otherproject.[ProjectStart]
  ,[OtherProjectEnd] = otherproject.[ProjectEnd] 
  ,otherproject.[OverlapDays]
  ,[TotalProjectDays] = datediff(d,EmployeeProject.[ProjectStart],EmployeeProject.[ProjectEnd]) + 1
  from #temp EmployeeProject
  outer apply
  (
    select 
    [ProjectId]
    ,[ProjectStart]
    ,[ProjectEnd]
    ,[OverlapDays] = [dbo].[SIP_NPT_fGetDateWindowsOverlap](EmployeeProject.[ProjectStart],EmployeeProject.[ProjectEnd],[ProjectStart],[ProjectEnd])
    from #temp
    where [EmployeeId] = EmployeeProject.[EmployeeId] and [ProjectId] <> EmployeeProject.[ProjectId]
  ) as otherproject
) as f1
order by [EmployeeId],[ProjectId],[ProjectStart],[OtherProjectStart]

--DECLARE A TEMP TABLE

create table #temp

(

[EmployeeId] nvarchar(100)

,[ProjectId] nvarchar(100)

,[ProjectStart] date

,[ProjectEnd] date

)

--FILL TEMP TABLE WITH DATA HERE

...

--DETERMINE THE OVERLAP FOR EACH EMPLOYEE PROJECT TO EVERY OTHER EMPLOYEE PROJECT

select *

,[PercentOverlap] =

case when [TotalProjectDays] <> 0 and [OverlapDays] is not null then ([OverlapDays] / convert(decimal(18,2),[TotalProjectDays])) * 100

else 0

end

from

(

select

EmployeeProject.*

,[OtherProjectId] = otherproject.[ProjectId]

,[OtherProjectStart] = otherproject.[ProjectStart]

,[OtherProjectEnd] = otherproject.[ProjectEnd]

,otherproject.[OverlapDays]

,[TotalProjectDays] = datediff(d,EmployeeProject.[ProjectStart],EmployeeProject.[ProjectEnd]) + 1

from #temp EmployeeProject

outer apply

(

select

[ProjectId]

,[ProjectStart]

,[ProjectEnd]

,[OverlapDays] = [dbo].[SIP_NPT_fGetDateWindowsOverlap](EmployeeProject.[ProjectStart],EmployeeProject.[ProjectEnd],[ProjectStart],[ProjectEnd])

from #temp

where [EmployeeId] = EmployeeProject.[EmployeeId] and [ProjectId] <> EmployeeProject.[ProjectId]

) as otherproject

) as f1

order by [EmployeeId],[ProjectId],[ProjectStart],[OtherProjectStart]