create table #qj(personcode varchar(10),starttime datetime,endtime datetime)
insert #qj select
'000001','2007-05-10 13:15:00.000','2007-05-10 16:15:00.000' insert #qj select
'000002','2007-05-16 08:15:00.000','2007-05-18 13:15:00.000' insert #qj select
'000001','2007-05-11 13:15:00.000','2007-05-12 16:15:00.000' insert #qj select
'000002','2007-05-19 08:15:00.000','2007-05-20 13:15:00.000'select personcode,sum(
datediff(ss,
(case when '2007-05-16'>starttime then '2007-05-16' else starttime end),
(case when dateadd(dd,1,'2007-05-19')<endtime then dateadd(dd,1,'2007-05-19') else endtime end))
-9000*datediff(dd,
(case when '2007-05-16'>starttime then '2007-05-16' else starttime end),
(case when dateadd(dd,1,'2007-05-19')<endtime then dateadd(dd,1,'2007-05-19') else endtime end))
)秒差
from #qj
where not(starttime>=dateadd(dd,1,'2007-05-19') or endtime<='2007-05-16')
group by personcodepersoncode 秒差
---------- -----------
000002 220500(所影响的行数为 1 行)
select datediff(ss,'2007-05-05 05:30:00','2007-05-05 08:00:00')--9000
insert #qj select
'000001','2007-05-10 13:15:00.000','2007-05-10 16:15:00.000' insert #qj select
'000002','2007-05-16 08:15:00.000','2007-05-18 13:15:00.000' insert #qj select
'000001','2007-05-11 13:15:00.000','2007-05-12 16:15:00.000' insert #qj select
'000002','2007-05-19 08:15:00.000','2007-05-20 13:15:00.000'select personcode,sum(
datediff(ss,
(case when '2007-05-16'>starttime then '2007-05-16' else starttime end),
(case when dateadd(dd,1,'2007-05-19')<endtime then dateadd(dd,1,'2007-05-19') else endtime end))
-9000*datediff(dd,
(case when '2007-05-16'>starttime then '2007-05-16' else starttime end),
(case when dateadd(dd,1,'2007-05-19')<endtime then dateadd(dd,1,'2007-05-19') else endtime end))
)秒差
from #qj
where not(starttime>=dateadd(dd,1,'2007-05-19') or endtime<='2007-05-16')
group by personcodepersoncode 秒差
---------- -----------
000002 220500(所影响的行数为 1 行)
select datediff(ss,'2007-05-05 05:30:00','2007-05-05 08:00:00')--9000
班时间到第2天上班时间这段时间 是9000秒
-9000*datediff(dd,
(case when '2007-05-16'>starttime then '2007-05-16' else starttime end),
(case when dateadd(dd,1,'2007-05-19')<endtime then dateadd(dd,1,'2007-05-19') else endtime end))
查询时间的开始与记录的开始比较,取最大值,
查询时间的结束与记录的结束比较,取最小值,
它们之间差的天数*每天差的9000秒不是你要的?
insert #qj select
'000001','2007-05-10 13:15:00.000','2007-05-10 16:15:00.000' insert #qj select
' 000002','2007-05-16 08:15:00.000','2007-05-18 13:15:00.000' insert #qj select
'000001','2007-05-11 13:15:00.000','2007-05-12 16:15:00.000' insert #qj select
'000002','2007-05-19 08:15:00.000','2007-05-20 13:15:00.000'
---参数
declare @BeginDate datetime
declare @EndDate datetime--查询条件
set @BeginDate='2007-5-16 8:00'
set @endDate='2007-5-19 17:30'--思路是先按天拆分请假记录
declare @BeginDate1 datetime
declare @EndDate1 datetime
set @BeginDate1=@BeginDate
set @EndDate1=convert(varchar(10),@BeginDate,120)+' 17:30'declare @t table(personcode varchar(10),starttime datetime,endtime datetime)--循环,每天处理一次
while @EndDate1<=@EndDate
begin insert @t
select personcode,
case when starttime<@BeginDate1 then @BeginDate1 else starttime end as starttime,
case when endtime>@endDate1 then @endDate1 else endtime end as endtime
from #qj
where starttime between @BeginDate1 and @endDate1
or endtime between @BeginDate1 and @endDate1
set @beginDate1=dateadd(day,1,@beginDate1)
set @EndDate1=dateadd(day,1,@endDate1)
end--再汇总计算秒数
select personcode,sum(datediff(ss,starttime,endtime)) as times
from @t
group by personcode--结果(没验证,不知道对不对)
personcode times
---------- -----------
000002 85500(所影响的行数为 1 行)--删除环境
drop table #qj
insert #qj select
'000001','2007-05-10 13:15:00.000','2007-05-10 16:15:00.000' insert #qj select
' 000002','2007-05-16 08:15:00.000','2007-05-18 13:15:00.000' insert #qj select
'000001','2007-05-11 13:15:00.000','2007-05-12 16:15:00.000' insert #qj select
'000002','2007-05-19 08:15:00.000','2007-05-20 13:15:00.000'
---参数
declare @BeginDate datetime
declare @EndDate datetime--查询条件
set @BeginDate='2007-5-16 8:00'
set @endDate='2007-5-19 17:30'--思路是先按天拆分请假记录
declare @BeginDate1 datetime
declare @EndDate1 datetime
set @BeginDate1=@BeginDate
set @EndDate1=convert(varchar(10),@BeginDate,120)+' 17:30'declare @t table(personcode varchar(10),starttime datetime,endtime datetime)--循环,每天处理一次
while @EndDate1<=@EndDate
begin insert @t
select personcode,
case when starttime<@BeginDate1 then @BeginDate1 else starttime end as starttime,
case when endtime>@endDate1 then @endDate1 else endtime end as endtime
from #qj
where @BeginDate1 between starttime and endtime
or @enddate1 between starttime and endtime
set @beginDate1=dateadd(day,1,@beginDate1)
set @EndDate1=dateadd(day,1,@endDate1)
end--再汇总计算秒数
select personcode,sum(datediff(ss,starttime,endtime)) as times
from @t
group by personcode--结果(没验证,不知道对不对)
personcode times
---------- -----------
000002 119700(所影响的行数为 1 行)
--删除环境
drop table #qj
2007-5-11 8:00
2007-5-19 17:30结果是:
personcode times
---------- -----------
000001 45000
000002 119700(所影响的行数为 2 行)
兴奋得蒙住了眼睛
select personcode,sum(
datediff(ss,
(case when '2007-05-16 08:00:00'>starttime then '2007-05-16 08:00:00' else starttime end),
(case when '2007-05-19 17:30:00'<endtime then '2007-05-19 17:30:00' else endtime end))
-52200*datediff(dd,
(case when '2007-05-16 08:00:00'>starttime then '2007-05-16 08:00:00' else starttime end),
(case when '2007-05-19 17:30:00'<endtime then '2007-05-19 17:30:00' else endtime end))
)秒差
from #qj
where not(starttime>=dateadd(dd,1,'2007-05-19') or endtime<='2007-05-16')
group by personcode
------------
personcode 秒差
---------- -----------
000002 119700(所影响的行数为 1 行)
select datediff(ss,'2007-05-05 17:30:00','2007-05-06 08:00:00')--52200
下面改成参数
declare @a datetime,@b datetime
select @a='2007-05-11 08:00:00',@b='2007-05-19 17:30:00'
--select @a='2007-05-16 08:00:00',@b='2007-05-19 17:30:00'
select personcode,sum(
datediff(ss,
(case when @a>starttime then @a else starttime end),
(case when @b<endtime then @b else endtime end))
-52200*datediff(dd,
(case when @a>starttime then @a else starttime end),
(case when @b<endtime then @b else endtime end))
)秒差
from #qj
where not(starttime>=dateadd(dd,1,@b) or endtime<=@a)
group by personcode
---------------
personcode 秒差
---------- -----------
000001 45000
000002 119700(所影响的行数为 2 行)