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