sqlserver 各种集合、区间、 时间轴(持更)

1.所有有交集的区间
场景：在事件表里查找某年员工的岗位系数，并计算其加权平均数。case1：该员工是老员工，从2020年一直到2049年。case2：该员工是老员工，但是今年离职。case3：该员工是今年的新员工，今年入职，今年离职。case4：该员工是今年的新员工，一直在职。case5:该员工是今年的新员工，未来离职。

select * from A where datediff(MINUTE,FromDate,CONVERT(CHAR(5),{TERM},120)+'12-31')>0 and datediff(MINUTE ,CONVERT(CHAR(5),{TERM},120)+'01-01' ,ToDate )>0 
1

2.部分有交集
场景：

 select * from  A where ((datediff(MINUTE,@begintime,endtime)>0  and datediff(MINUTE ,BeginTime ,@BeginTime )>0)
				or (  datediff(MINUTE,@EndTime ,endtime)>0  and datediff(MINUTE ,BeginTime ,@EndTime )>0 ))
1
2

3.只有子集
场景：

select * from A where @BeginTime>BeginTime And EndTime>@EndTime
1

4.没交集
场景：

select * from A Where @BeginTime>a.EndTime
			Or BeginTime>@EndTime
1
2

5.取交集外的部分
场景

	Select 
		Case When b.BeginTime Between a.BeginTime And a.EndTime And b.EndTime>=a.EndTime Then a.BeginTime Else b.EndTime End
		,Case When a.BeginTime Between b.BeginTime And b.EndTime And a.EndTime>=b.EndTime Then a.EndTime Else b.BeginTime End
from A a,B b		
	Where (a.BeginTime<>b.BeginTime Or a.EndTime<>b.EndTime)
		And ((b.BeginTime Between a.BeginTime And a.EndTime And b.EndTime>=a.EndTime)
			Or (a.BeginTime Between b.BeginTime And b.EndTime And a.EndTime>=b.EndTime))
		And Case When b.BeginTime Between a.BeginTime And a.EndTime And (b.EndTime>=a.EndTime) Then a.BeginTime Else b.EndTime End
			<Case When a.BeginTime Between b.BeginTime And b.EndTime And (a.EndTime>=b.EndTime) Then a.EndTime Else b.BeginTime End
1
2
3
4
5
6
7
8
9

6.取交集部分
场景

	Select Case When b.BeginTime Between a.BeginTime And a.EndTime And b.EndTime>=a.EndTime Then b.begintime Else a.begintime End
	,Case When a.BeginTime Between b.BeginTime And b.EndTime And a.EndTime>=b.EndTime Then a.endtime Else b.endtime End
From A a,B b
Where (a.BeginTime<>b.BeginTime Or a.EndTime<>b.EndTime)
	And ((b.BeginTime Between a.BeginTime And a.EndTime And b.EndTime>=a.EndTime)
		Or (a.BeginTime Between b.BeginTime And b.EndTime And a.EndTime>=b.EndTime))
	And Case When b.BeginTime Between a.BeginTime And a.EndTime And (b.EndTime>=a.EndTime) Then a.BeginTime Else b.EndTime End
		<Case When a.BeginTime Between b.BeginTime And b.EndTime And (a.EndTime>=b.EndTime) Then a.EndTime Else b.BeginTime End
1
2
3
4
5
6
7
8

7.一般的取交集（左端都要取最大，右端取最小）
场景：

Select 
	Case When BeginDate<@BeginDate Then @BeginDate Else BeginDate End,		
	Case When EndDate>@EndDate Then @EndDate Else EndDate End
1
2
3