复杂sql处理逻辑 case when的用法

场景：

实习计划中，包括轮换计划，个人计划。轮换计划之间不会存在时间叠加。个人计划于轮换计划之间可能会存在时间叠加。叠加部分以个人计划为准。计划状态包括：未开始，实习中，实习中断，实习结束。状态根据开始时间，结束时间与当前时间的关系来计算。但是需要通过sql处理，叠加状态时间判断。

图例：

假如当前系统时间是2019-11-26，序号3的状态应该为实习中，序号4的状态应该为实习中断。

假如当前系统时间为2019-11-29，序号2的状态是实习中，序号3的状态是实习结束，序号4的状态是实习中断。

假如当前系统时间为2019-12-01，序号2的状态是实习结束，序号3的状态是实习结束，序号4的状态是实习中。

说明：

1、当是个人计划时，只要当前系统时间是处于该计划的范围内，该计划就是实习中。（比如图一、图二）

2、当是轮换计划时，要分时间考虑。如果该时间段内，且是当前系统时间所处的时间范围，没有其他个人计划时，该轮换计划就是实习中。（比如上图三）而改时间段内，是当前系统时间所处时间范围，有其他个人计划时，该轮换计划就是实习中断。（比如上图一、图二）

背景：

后台采用三个表trainee_info 实习生表 trainee_rotation_program 计划表 trainee_group_info 轮换分组表

计划表中存在 trainee_id 实习生id 、group_id 分组id 、plan_type 计划类型

计划类型为1或者为null的是轮换计划，关联分组id 计划类型为2的是个人计划，关联实习生id

数据库保存的时间格式是年月日时分秒的但是时分秒全都是0 比如2019-11-26 00:00:000

所以写sql判断时间时要注意时分秒的处理

sql：

 select a.name,c.structure_name as  dept_name,b.starttime,b.endtime,
	 
	  (
	 <!-- 第一种情况 个人计划  只要当前时间在范围内  优先个人计划   个人计划实习中--> 
	 <!-- 第二种情况 轮科计划  当前时间在范围内，且当前时间 存在其他个人计划优先于轮科计划  轮科计划 实习中断 -->
	 <!-- 第三种情况 轮科计划  当前时间在范围内，且当前时间 不存在其他个人计划优先轮科计划的情况  轮科计划 实习中  -->
	 <!-- 第四种情况 任何计划  当前时间小于开始时间  未开始-->
	 <!-- 第五种情况 任何计划  当前时间大于结束时间  实习结束-->
	  <![CDATA[
	 			case when b.plan_type='2' and DATEADD(DAY,1,b.endtime) > GETDATE() and GETDATE() > b.starttime then  1 
	 				when (b.plan_type is null or b.plan_type='1') and DATEADD(DAY,1,b.endtime) > GETDATE() and GETDATE() > b.starttime
	 				and 
					(
						 select count(1) from (
									select * from trainee_rotation_program t where  
									(t.trainee_id =a.id) 
									and 
									(
										(b.starttime <= t.starttime  and t.starttime <= b.endtime)  
										or (t.endtime >= b.starttime and t.endtime <= b.endtime) 
										or (t.starttime >= b.starttime and t.endtime <= b.endtime)
									 )
									and 
									(DATEADD(DAY,1,t.endtime) > GETDATE() and GETDATE() > t.starttime)
						 ) aa
					)>0 then 3
	 				when (b.plan_type is null or b.plan_type='1') and DATEADD(DAY,1,b.endtime) > GETDATE() and GETDATE() > b.starttime
	 				and 
					(
						 select count(1) from (
									select * from trainee_rotation_program t where  
									(t.trainee_id =a.id) 
									and 
									(
										(b.starttime <= t.starttime  and t.starttime <= b.endtime)  
										or (t.endtime >= b.starttime and t.endtime <= b.endtime) 
										or (t.starttime >= b.starttime and t.endtime <= b.endtime)
									 )
									and 
									(DATEADD(DAY,1,t.endtime) > GETDATE() and GETDATE() > t.starttime)
						 ) aa
					)=0 then 1
					when GETDATE() < b.starttime then 0
					when GETDATE() > DATEADD(DAY,1,b.endtime) then 2 else 2 end 
				 ]]>
				  ) curstate,
		      a.starttime as userstar,a.endtime as userend,b.plan_type as plan_type,
	  (case when (b.plan_type=1 or b.plan_type is null)  then '轮换计划('+g.name+')' 
	  		when b.plan_type=2 then '个人计划' else '' end) as planSource,
	 orderid
	  from  trainee_info a
	   LEFT JOIN trainee_rotation_program b on (a.group_id = b.group_id or b.trainee_id=a.id)
	   left join trainee_group_info g on b.group_id = g.id
	   LEFT JOIN org_structure c on b.dept_id=c.id 
	where a.id=#{id} and b.endtime>a.starttime
	order by orderid,b.starttime desc
	<!-- 先根据排序号排序，再根据开始时间排序，用于个人计划的插单排序 -->

查询sql结果：（id=63）