34,594
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[Worktime] (
[I_D] [int] NOT NULL ,
[Bc] [smallint] NOT NULL , --班次
[SbTime] [datetime] NOT NULL , --上班时间
[XbTime] [datetime] NOT NULL --下班时间
) ON [PRIMARY]
insert into Worktime (I_D,Bc,SbTime,XbTime)VALUES(1,0,'2008-12-01 08:00','2008-12-01 11:30')
insert into Worktime (I_D,Bc,SbTime,XbTime)VALUES(2,0,'2008-12-01 12:30','2008-12-01 20:00')
insert into Worktime (I_D,Bc,SbTime,XbTime)VALUES(3,1,'2008-12-01 20:00','2008-12-01 23:30')
insert into Worktime (I_D,Bc,SbTime,XbTime)VALUES(4,1,'2008-12-02 00:30','2008-12-02 08:00')
insert into Worktime (I_D,Bc,SbTime,XbTime)VALUES(5,0,'2008-12-02 08:00','2008-12-02 11:30')
insert into Worktime (I_D,Bc,SbTime,XbTime)VALUES(6,0,'2008-12-02 12:30','2008-12-02 20:00')
insert into Worktime (I_D,Bc,SbTime,XbTime)VALUES(7,1,'2008-12-02 20:00','2008-12-02 23:30')
insert into Worktime (I_D,Bc,SbTime,XbTime)VALUES(8,1,'2008-12-03 00:30','2008-12-03 08:00')
--select * from Worktime
declare @Time1 datetime,@Time2 datetime
set @Time1 ='2008-12-01 21:20' --任务开始时间
set @Time2 ='2008-12-10 04:45' --任务结束时间
select 总有效时间=cast(sum(datediff(n,case when SbTime<@Time1 then @Time1 else SbTime end,case when XbTime>@Time2 then @Time2 else XbTIme end))/60.0 as decimal(18,1))
from Worktime
where XbTime>@Time1 and SbTime<@Time2
/*
总有效时间
---------------
31.7
*/
select bc,convert(varchar(10),sbtime,120) as date ,sum(datediff(hh,sbtime,xbtime))as time from worktime
group by bc,convert(varchar(10),sbtime,120)
select bc,sum(datediff(hh,sbtime,xbtime))from worktime
group by bc,convert(varchar(10),sbtime,120)