22,210
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([IHEHEnterDate] Date,[IHEHLeaveDate] Date)
Insert #A
select '2017-08-26','2017-09-01' union all
select '2017-08-26','2017-08-30' union all
select '2017-08-28','2017-09-01' union all
select '2017-08-28','2017-08-30'
Go
--测试数据结束
SELECT CASE WHEN IHEHEnterDate < '2017-08-27'
AND IHEHLeaveDate > '2017-08-31'
THEN DATEDIFF(DAY, '2017-08-27', '2017-08-31')
WHEN IHEHEnterDate < '2017-08-27'
AND IHEHLeaveDate < '2017-08-31'
THEN DATEDIFF(DAY, '2017-08-27', IHEHLeaveDate)
WHEN IHEHEnterDate >= '2017-08-27'
AND IHEHLeaveDate > '2017-08-31'
THEN DATEDIFF(DAY, IHEHEnterDate, '2017-08-31')
WHEN IHEHEnterDate >= '2017-08-27'
AND IHEHLeaveDate < '2017-08-31'
THEN DATEDIFF(DAY, IHEHEnterDate, IHEHLeaveDate)
ELSE 0
END AS 天数
FROM #A
SELECT SUM(CASE WHEN IHEHEnterDate < '2017-08-27'
AND IHEHLeaveDate > '2017-08-31'
THEN DATEDIFF(DAY, '2017-08-27', '2017-08-31')
WHEN IHEHEnterDate < '2017-08-27'
AND IHEHLeaveDate < '2017-08-31'
THEN DATEDIFF(DAY, '2017-08-27', IHEHLeaveDate)
WHEN IHEHEnterDate >= '2017-08-27'
AND IHEHLeaveDate > '2017-08-31'
THEN DATEDIFF(DAY, IHEHEnterDate, '2017-08-31')
WHEN IHEHEnterDate >= '2017-08-27'
AND IHEHLeaveDate < '2017-08-31'
THEN DATEDIFF(DAY, IHEHEnterDate, IHEHLeaveDate)
ELSE 0
END) AS 天数
FROM #A