22,209
社区成员
发帖
与我相关
我的任务
分享
select userid,min(dbenginddatetime) dbenginddatetime ,max(denddatetime) denddatetime,sum(nLeaveHours) nLeaveHours
from table
where datediff(d,dbenginddatetime,denddatetime)=0
group by userid
select userid, min(dbenginddatetime) ,max(denddatetime ),sum(nLeaveHours)
from tb
where dbenginddatetime>convert(varchar(10),getdate(),121) and denddatetime <convert(varchar(10),getdate()+1,121)
group by userid
create table tb(userid varchar(10) , dbenginddatetime datetime,denddatetime datetime,nLeaveHours int)
insert into tb values('aa' ,'2010-04-01 08:00:00', '2010-04-01 17:30:00', 8)
insert into tb values('aa' ,'2010-04-01 19:00:00', '2010-04-01 22:00:00', 3)
go
--简单的算法
select userid , min(dbenginddatetime) , max(denddatetime) , sum(nLeaveHours)
from tb
group by userid
/*
userid
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
aa 2010-04-01 08:00:00.000 2010-04-01 22:00:00.000 11
(所影响的行数为 1 行)
*/
--严谨的算法
select userid , min(dbenginddatetime) , max(denddatetime) , sum(nLeaveHours)
from tb
group by userid , convert(varchar(10),dbenginddatetime,120),convert(varchar(10),denddatetime,120)
/*
userid
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
aa 2010-04-01 08:00:00.000 2010-04-01 22:00:00.000 11
(所影响的行数为 1 行)
*/
drop table tb
select userid,min(dbenginddatetime),max(denddatetime),sum(nLeaveHours) from tbl
group by userid