具体要求如下:
1、从7点(大于等于7点)开始到晚上19点(小于19点),为正常工作时间,不计算在加班时间之内;
2、给定一个起始时间和结束时间,计算加班的个数,一个完整的小时算一个数,此外还要得到剩余的分钟数,再对分钟进行处理,小于30分钟忽略,如果大于30分钟,加班个数加1;
不知道我描述的是否清楚,我自己也做了算法,但总感觉不对,不知道各位大侠能否给予帮助,不胜感谢!
1、从7点(大于等于7点)开始到晚上19点(小于19点),为正常工作时间,不计算在加班时间之内;
2、给定一个起始时间和结束时间,计算加班的个数,一个完整的小时算一个数,此外还要得到剩余的分钟数,再对分钟进行处理,小于30分钟忽略,如果大于30分钟,加班个数加1;
不知道我描述的是否清楚,我自己也做了算法,但总感觉不对,不知道各位大侠能否给予帮助,不胜感谢!
详情请参照 http://topic.csdn.net/u/20100716/19/6f132f16-20e4-418c-8dee-b99d5f86d320.html
(
id int identity(1,1),
start datetime,
endt datetime
)insert into #temp
select '2010-8-4 6:23','2010-8-4 20:32'
union select '2010-8-4 5:23','2010-8-4 18:32'
union select '2010-8-4 7:23','2010-8-4 20:28'
union select '2010-8-4 6:57','2010-8-4 21:52'
union select '2010-8-4 6:33','2010-8-4 20:14'select *,
加班个数=(datediff(mi,start,endt)-720)/60+case when (datediff(mi,start,endt)-720)%60>=30 then 1 else 0 end
from #temp/*
结果
id start endt 加班个数
1 2010-08-04 06:23:00.000 2010-08-04 20:32:00.000 2
2 2010-08-04 05:23:00.000 2010-08-04 18:32:00.000 1
3 2010-08-04 06:33:00.000 2010-08-04 20:14:00.000 2
4 2010-08-04 06:57:00.000 2010-08-04 21:52:00.000 3
5 2010-08-04 07:23:00.000 2010-08-04 20:28:00.000 1
*/
(
id int identity(1,1),
start datetime,
endt datetime,
加班个数 int
)insert into #temp
select '2010-8-4 6:23','2010-8-4 20:32',0
union select '2010-8-4 5:23','2010-8-4 18:32',0
union select '2010-8-4 7:23','2010-8-4 20:28',0
union select '2010-8-4 6:57','2010-8-4 21:52',0
union select '2010-8-4 6:33','2010-8-4 20:14',0update #temp set 加班个数=(datediff(mi,start,endt)-720)/60+case when (datediff(mi,start,endt)-720)%60>=30 then 1 else 0 end select * from #temp
/*
结果
id start endt 加班个数
1 2010-08-04 05:23:00.000 2010-08-04 18:32:00.000 1
2 2010-08-04 06:23:00.000 2010-08-04 20:32:00.000 2
3 2010-08-04 06:33:00.000 2010-08-04 20:14:00.000 2
4 2010-08-04 06:57:00.000 2010-08-04 21:52:00.000 3
5 2010-08-04 07:23:00.000 2010-08-04 20:28:00.000 1
*/
OS(操作系统): Win2003]
DB(数据库版本): MSSQL2005企业版
问题描述:计算开始时间和结束时间的时间差,但其中要去除大于等于7点,小于19点的数据段的值;表信息:
加班表 --表名
开始时间 结束时间 加班标志 加班个数(结果列)
2010-02-01 02:10 2010-02-01 20:20 1 6
2010-02-01 13:00 2010-02-01 15:00 0 0(在工作时间段)
2010-02-01 23:00 2010-02-01 01:00 1 2
insert into #temp
select '2010/02/01 19:00','2010/02/01 20:31',0
怎么结果是 -10?
nightmaple 的算法,如果有人早退加班时间会是负数。应该再加个判断语句。
insert into 加班表
select '2010-02-01 02:10', '2010-02-01 20:20', 1 union all
select '2010-02-01 13:00', '2010-02-01 15:00', 0 union all
select '2010-02-01 23:00', '2010-02-02 01:00', 1
select 开始时间,结束时间,加班标志,
加班个数= case when convert(varchar(8),开始时间,108)< '07:00' and convert(varchar(8),结束时间,108)<='19:00' then case when DATEDIFF(MINUTE,convert(varchar(8),开始时间,108),'7:00')%60>=30 then DATEDIFF(HOUR,convert(varchar(8),开始时间,108),'7:00')
else DATEDIFF(HOUR,convert(varchar(8),开始时间,108),'7:00')-1 end
when convert(varchar(8),开始时间,108)< '07:00' and convert(varchar(8),结束时间,108)>'19:00' then case when (DATEDIFF(MINUTE,开始时间,结束时间)-720)%60>=30 then DATEDIFF(HOUR,开始时间,结束时间)-11
else DATEDIFF(HOUR,开始时间,结束时间)-12 end
when convert(varchar(8),开始时间,108)>= '07:00' and convert(varchar(8),开始时间,108)<= '19:00' and convert(varchar(8),结束时间,108)<='19:00' then 0
when convert(varchar(8),开始时间,108)>= '07:00' and convert(varchar(8),开始时间,108)<= '19:00' and convert(varchar(8),结束时间,108)>'19:00' then case when DATEDIFF(MINUTE,'19:00',convert(varchar(8),结束时间,108))%60>=30 then DATEDIFF(HOUR,'19:00',convert(varchar(8),结束时间,108))+1
else DATEDIFF(HOUR,'19:00',convert(varchar(8),结束时间,108)) end
else case when DATEDIFF(MINUTE,开始时间,结束时间)%60>=30 then DATEDIFF(HOUR,开始时间,结束时间)+1
else DATEDIFF(HOUR,开始时间,结束时间) end end
from 加班表
开始时间 结束时间 加班标志 加班个数
2010-02-01 02:10:00.000 2010-02-01 20:20:00.000 1 6
2010-02-01 13:00:00.000 2010-02-01 15:00:00.000 0 0
2010-02-01 23:00:00.000 2010-02-02 01:00:00.000 1 2
楼主的测试数据好像有点问题,改了一下出的结果
那就加多一个判断,
如果上班时间不足12个钟就算加班个数为0,做法如下
update #temp set 加班个数=
case when datediff(mi,start,endt)<=720 then 0
else
(datediff(mi,start,endt)-720)/60+case when (datediff(mi,start,endt)-720)%60>=30 then 1 else 0 end
endselect * from #temp/*
结果
id start endt 加班个数
1 2010-02-01 19:00:00.000 2010-02-01 20:31:00.000 0
2 2010-08-04 05:23:00.000 2010-08-04 18:32:00.000 1
3 2010-08-04 06:23:00.000 2010-08-04 20:32:00.000 2
4 2010-08-04 06:33:00.000 2010-08-04 20:14:00.000 2
5 2010-08-04 06:57:00.000 2010-08-04 21:52:00.000 3
6 2010-08-04 07:23:00.000 2010-08-04 20:28:00.000 1
*/如果小于7点,大于19点的全部算加班,做法如下update #temp set 加班个数=
(datediff(mi,
case when datepart(hh, start)<7 then start
else convert(varchar(10),start,112)+' 7:00' end,
case when datepart(hh, endt)>19 then endt
else convert(varchar(10),endt,112)+' 19:00' end
)-720)/60+case when
(datediff(mi,case when datepart(hh, start)<7 then start
else convert(varchar(10),start,112)+' 7:00' end,
case when datepart(hh, endt)>19 then endt
else convert(varchar(10),endt,112)+' 19:00' end)-720)%60>=30 then 1 else 0 end /*
结果
id start endt 加班个数
1 2010-02-01 19:00:00.000 2010-02-01 20:31:00.000 2
2 2010-08-04 05:23:00.000 2010-08-04 18:32:00.000 2
3 2010-08-04 06:23:00.000 2010-08-04 20:32:00.000 2
4 2010-08-04 06:33:00.000 2010-08-04 20:14:00.000 2
5 2010-08-04 06:57:00.000 2010-08-04 21:52:00.000 3
6 2010-08-04 07:23:00.000 2010-08-04 20:28:00.000 1
*/
(
id int identity(1,1),
start datetime,
endt datetime,
加班个数 int
)insert into #temp
select '2010-02-01 5:00','2010-02-01 6:20',0
union select '2010-02-01 6:00','2010-02-01 8:00',0
union select '2010-02-01 6:00','2010-02-01 20:00',0
union select '2010-02-01 13:00','2010-02-01 15:00',0
union select '2010-02-01 13:10','2010-02-01 20:20',0
union select '2010-02-01 19:40','2010-02-01 21:00',0
union select '2010-02-01 22:00','2010-02-02 01:00',0--算出每条记录正常上班的分钟,临时存放在“加班个数”字段
update #temp set 加班个数=datediff(mi,
case when datepart(hh,start)<7 then convert(varchar(10),start,112)+' 7:00'
else start end,
case when datediff(mi,convert(varchar(10),start,112)+' 7:00',endt)>720
then convert(varchar(10),start,112)+' 19:00'
else endt end)--上下班的分钟差-正常上班的分钟=加班的分钟
update #temp set 加班个数=(datediff(mi,start,endt)-case when 加班个数>0 then 加班个数 else 0 end)/60
+case when (datediff(mi,start,endt)-case when 加班个数>0 then 加班个数 else 0 end)%60>=30 then 1 else 0 end select * from #temp/*
结果
id start endt 加班个数
1 2010-02-01 13:00:00.000 2010-02-01 15:00:00.000 0
2 2010-02-01 13:10:00.000 2010-02-01 20:20:00.000 1
3 2010-02-01 19:40:00.000 2010-02-01 21:00:00.000 1
4 2010-02-01 22:00:00.000 2010-02-02 01:00:00.000 3
5 2010-02-01 05:00:00.000 2010-02-01 06:20:00.000 1
6 2010-02-01 06:00:00.000 2010-02-01 20:00:00.000 2
7 2010-02-01 06:00:00.000 2010-02-01 08:00:00.000 1
*/