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

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