22,209
社区成员
发帖
与我相关
我的任务
分享
create table TEXT2
(
SDATE datetime,
EDATE datetime,
num int)
自己将数据导入进去
下面是结果代码
WITH CTE_T1 AS(
select * from TEXT2 where EDATE IS NULL)
,CTE_T2 AS(
select * from TEXT2 where EDATE IS NOT NULL
union all
select MAX(SDATE)SDATE,MAX(SDATE)EDATE,0 NUM from TEXT2)
,CTE_T3 AS(
select A.*,B.EDATE EDATE2 from CTE_T2 A
left join CTE_T2 B on A.EDATE>B.EDATE)
,CTE_T4 AS(
select *,row_number() over(partition by SDATE ORDER BY EDATE2 desc) ID from CTE_T3)
,CTE_T5 AS(
select * from CTE_T4 where ID=1)
,CTE_T6 AS(
select A.*,isnull(B.NUM,0) NUM2 from CTE_T5 A
left join CTE_T1 B ON A.EDATE>=B.SDATE and (A.EDATE2<B.SDATE or A.EDATE2 is null))
,CTE_T7 AS(
select SDATE,EDATE,NUM,SUM(NUM2) NUM2 FROM CTE_T6 group by SDATE,EDATE,NUM)
,CTE_T8 AS(
select SDATE,EDATE,case when NUM+NUM2>0 and NUM2<0 then 0 else NUM+NUM2 end NUM from CTE_T7)
select sum(num) 剩余天数 from CTE_T8