求出给定两日期段 之间的交集算法

---Sqlserver 存储过程
---本例的实现虽用sqlserver ，其实同样可以应用到其它的语言中进行解决，因此将其作如下描述
---一点小小经验希望能解决一些小的问题 呵呵
--欢迎大家交流

Create Procedure pGet_DateAmbitIntersection
(@A datetime, @B datetime, @L datetime, @R datetime ,
 @Result_L datetime output, @Result_R datetime output, @Result_LR int output)
---得到日期的交集
--@A 要进行比较的日期范围左值
--@B 要进行比较的日期范围右值
--@L 标准日期范围左值
--@R 标准日期范围右值
As
Begin
/*
[问题的提出]
假设有 A,B 与 L,R 四点, 其中 AB , 与 LR 为两连接在一起的线段.求 在不同条件下 AB 与 LR 的交集?

[算法理论]
现用C# 伪代码 描述如下:

if ( AB < LR )
 if ( A < L And A < R And B > L And B < R )  Return LB;
 if ( A = L And A < R And B > L And B < R )  Return AB;
 if ( A > L And A < R And B > L And B < R )  Return AB;
 if ( A > L And A < R And B > L And B = R )  Return AB;
 if ( A > L And A < R And B > L And B > R )  Return AR;
if ( AB = LR )
 if ( A < L And A < R And B > L And B < R )  Return LB;
 if ( A = L And A < R And B > L And B = R )  Return AB;
 if ( A > L And A < R And B > L And B > R )  Return AR;
if ( AB > LR )
 if ( A < L And A < R And B > L And B < R )  Return LB;
 if ( A < L And A < R And B = L And B = R )  Return LB;
 if ( A < L And A < R And B > L And B > R )  Return LR;
 if ( A = L And A < R And B > L And B > R )  Return LR;
 if ( A > L And A < R And B > L And B > R )  Return AL;
*/
--[问题的应用]
--- Declare @Result_LR int

 Declare @LR int, @AB int
 Select @LR = DATEDIFF(day, @L, @R)
 Select @AB = DATEDIFF(day, @A, @B)

 Select @Result_L = '1900-01-01'
 Select @Result_R = '1900-01-01'
 Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)

Print @A
Print @B
Print @L
Print @R
Print @AB
Print @LR

 if (@LR <= 0 or @AB <= 0)
 Begin
  Return
 End
---if ( AB < LR )
 if (@AB < @LR)
 Begin
--- if ( A < L And A < R And B > L And B < R )  Return LB;
  if ( @A < @L And @A < @R And @B > @L And @B < @R )
  Begin
   Select @Result_L = @L
   Select @Result_R = @B
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '< 1'
   Return
  End
--- if ( A = L And A < R And B > L And B < R )  Return AB;
  if ( @A = @L And @A < @R And @B > @L And @B < @R )
  Begin
   Select @Result_L = @A
   Select @Result_R = @B
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '< 2'
   Return
  End
--- if ( A > L And A < R And B > L And B < R )  Return AB;
  if ( @A > @L And @A < @R And @B > @L And @B < @R )
  Begin
   Select @Result_L = @A
   Select @Result_R = @B
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '< 3'
   Return
  End
--- if ( A > L And A < R And B > L And B = R )  Return AB;
  if ( @A > @L And @A < @R And @B > @L And @B = @R )
  Begin
   Select @Result_L = @A
   Select @Result_R = @B
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '< 4'
   Return
  End
--- if ( A > L And A < R And B > L And B > R )  Return AR;
  if ( @A > @L And @A < @R And @B > @L And @B > @R )
  Begin
   Select @Result_L = @A
   Select @Result_R = @R
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '< 5'
   Return
  End
 End
---if ( AB = LR )
 if (@AB = @LR)
 Begin
--- if ( A < L And A < R And B > L And B < R )  Return LB;
  if ( @A < @L And @A < @R And @B > @L And @B < @R )
  Begin
   Select @Result_L = @L
   Select @Result_R = @B
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '= 1'
   Return
  End
--- if ( A = L And A < R And B > L And B = R )  Return AB;
  if ( @A = @L And @A < @R And @B > @L And @B = @R )
  Begin
   Select @Result_L = @A
   Select @Result_R = @B
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '= 2'
   Return
  End
--- if ( A > L And A < R And B > L And B > R )  Return AR;
  if ( @A > @L And @A < @R And @B > @L And @B > @R )
  Begin
   Select @Result_L = @A
   Select @Result_R = @R
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '= 3'
   Return
  End
 End
--if ( AB > LR )
 if (@AB > @LR)
 Begin
-- if ( A < L And A < R And B > L And B < R )  Return LB;
  if ( @A < @L And @A < @R And @B > @L And @B < @R )
  Begin
   Select @Result_L = @L
   Select @Result_R = @B
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '> 1'
   Return
  End
-- if ( A < L And A < R And B = L And B = R )  Return LB;
  if ( @A < @L And @A < @R And @B = @L And @B = @R )
  Begin
   Select @Result_L = @L
   Select @Result_R = @B
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '> 2'
   Return
  End
-- if ( A < L And A < R And B > L And B > R )  Return LR;
  if ( @A < @L And @A < @R And @B > @L And @B > @R )
  Begin
   Select @Result_L = @L
   Select @Result_R = @R
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '> 3'
   Return
  End
-- if ( A = L And A < R And B > L And B > R )  Return LR;
  if ( @A = @L And @A < @R And @B > @L And @B > @R )
  Begin
   Select @Result_L = @L
   Select @Result_R = @R
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '> 4'
   Return
  End
-- if ( A > L And A < R And B > L And B > R )  Return AR;
  if ( @A > @L And @A < @R And @B > @L And @B > @R )
  Begin
   Select @Result_L = @A
   Select @Result_R = @R
   Select @Result_LR = DATEDIFF(day, @Result_L, @Result_R)
Print '> 5'
   Return
  End
 End
End

 

GO