求出給定兩日期段 之間的交集演算法

---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