27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE (id INT PRIMARY KEY, Name NVARCHAR(20),BegTime datetime,EndTime datetime)
INSERT INTO @t VALUES (1,'王五','2017-11-06 08:00','2017-11-06 18:00')
INSERT INTO @t VALUES (2,'王二','2017-11-07 08:00','2017-11-07 18:00')
INSERT INTO @t VALUES (3,'王二','2017-11-08 08:00','2017-11-09 12:00')
INSERT INTO @t VALUES (4,'王五','2017-11-07 14:00','2017-11-08 18:00')
Select name,sum(days) as Days
From (Select id,name,
case when datepart(hour,begtime)>12 or datepart(hour,endtime)<18
then (0.5+datepart(d,endtime)-datepart(d,begtime))
else (1+datepart(d,endtime)-datepart(d,begtime)) end as days
From @t) a
Group By name
name Days
-------------------- ---------------------------------------
王二 2.5
王五 2.5