考勤计算方法,不足半小时以半小时记 考勤计算:不足半小时以半小时计如:1-30分钟 按0.5 31-60分钟 按1 61-90分钟 按1.5有没有通用公式,不想用太多的判断。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 declare @t int,@t1 decimal(10,1)select @t1=61select @t1=floor((@t1-1)/30+1)/2select @t1 select case when datediff(minute,时间,getdate())<=30 then 0.5 when datediff(minute,时间,getdate()) between 31 and 60 then 1 when datediff(minute,时间,getdate()) between 61 and 90 then 1.5 end as colfrom tb select floor((1.0-1)/30+1)/2select floor((30.0-1)/30+1)/2select floor((31.0-1)/30+1)/2select floor((60.0-1)/30+1)/2select floor((61.0-1)/30+1)/2select floor((100.0-1)/30+1)/2/*---------------------------------------0.500000(1 行受影响)---------------------------------------0.500000(1 行受影响)---------------------------------------1.000000(1 行受影响)---------------------------------------1.000000(1 行受影响)---------------------------------------1.500000(1 行受影响)---------------------------------------2.000000(1 行受影响)*/ select CEILING(@时间 * 1.0 / 30) / 2 as '结果' 真黑啊:是计算迟到早退的方法吧,加班就是不足30分钟按0分钟计了select ((1-1)/30+1)/2.0select ((30-1)/30+1)/2.0select ((31-1)/30+1)/2.0select ((60-1)/30+1)/2.0select ((61-1)/30+1)/2.0select ((100-1)/30+1)/2.0 create table tb(bdate datetime,edate datetime)insert into tbselect '2008-04-19 14:00','2008-04-19 14:02' union allselect '2008-04-19 14:00','2008-04-19 14:15' union allselect '2008-04-19 14:00','2008-04-19 14:35' union allselect '2008-04-19 14:00','2008-04-19 15:26' select bdate,edate,出勤分钟=datediff(mi,bdate,edate),出勤小时=((datediff(mi,bdate,edate)+30)/30)*0.5 from tb/*bdate edate 出勤分钟 出勤小时-------------------------------------------------------------2008-04-19 14:00:00.000 2008-04-19 14:02:00.000 2 .52008-04-19 14:00:00.000 2008-04-19 14:15:00.000 15 .52008-04-19 14:00:00.000 2008-04-19 14:35:00.000 35 1.02008-04-19 14:00:00.000 2008-04-19 15:26:00.000 86 1.5*/drop table tb 引用 7 楼 昨夜小楼你搞错了,这肯定是缺勤的计算方法,开玩笑,呵呵 ---------------------------------------------是啊,如果出勤这样算该有多好啊~ 改正一下,哈哈!create table tb(bdate datetime,edate datetime)insert into tbselect '2008-04-19 14:00','2008-04-19 14:02' union allselect '2008-04-19 14:00','2008-04-19 14:15' union allselect '2008-04-19 14:00','2008-04-19 14:35' union allselect '2008-04-19 14:00','2008-04-19 15:26' select bdate,edate,缺勤分钟=datediff(mi,bdate,edate),缺勤小时=((datediff(mi,bdate,edate)+30)/30)*0.5 from tb/*bdate edate 缺勤分钟 缺勤小时-------------------------------------------------------------2008-04-19 14:00:00.000 2008-04-19 14:02:00.000 2 .52008-04-19 14:00:00.000 2008-04-19 14:15:00.000 15 .52008-04-19 14:00:00.000 2008-04-19 14:35:00.000 35 1.02008-04-19 14:00:00.000 2008-04-19 15:26:00.000 86 1.5*/drop table tb =CEILING(A1 * 1 / 30, 1) / 2 请教一个简单关联查询SQL sql 基础问题 急急急!!!怎样将两列组合定义为主键? 懂这条查询语句的进来 SQL 2012安装问题 坑爹啊!!!!过程或函数需要参数 '@Tables',但未提供该参数。 问下,如何在一张表中随机取一条数据. 在线等,解决就结贴 INSERT INTO ASP访问SQL2005,并发连接数超过200,DELL 4CPU + 16G内存 ,结果CPU占用率依旧是100%,请高手给予解决办法,在线等 终于找到国内的免费JSP/Servlet空间了,想找地方练手的新手快来吧! 怎莫能够把一个字段的值:北京(11)显示为北京;11
select @t1=61
select @t1=floor((@t1-1)/30+1)/2
select @t1
when datediff(minute,时间,getdate()) between 31 and 60 then 1
when datediff(minute,时间,getdate()) between 61 and 90 then 1.5
end as col
from tb
select floor((30.0-1)/30+1)/2
select floor((31.0-1)/30+1)/2
select floor((60.0-1)/30+1)/2
select floor((61.0-1)/30+1)/2
select floor((100.0-1)/30+1)/2/*---------------------------------------
0.500000(1 行受影响)
---------------------------------------
0.500000(1 行受影响)
---------------------------------------
1.000000(1 行受影响)
---------------------------------------
1.000000(1 行受影响)
---------------------------------------
1.500000(1 行受影响)
---------------------------------------
2.000000(1 行受影响)*/
select ((1-1)/30+1)/2.0
select ((30-1)/30+1)/2.0
select ((31-1)/30+1)/2.0
select ((60-1)/30+1)/2.0
select ((61-1)/30+1)/2.0
select ((100-1)/30+1)/2.0
insert into tb
select '2008-04-19 14:00','2008-04-19 14:02' union all
select '2008-04-19 14:00','2008-04-19 14:15' union all
select '2008-04-19 14:00','2008-04-19 14:35' union all
select '2008-04-19 14:00','2008-04-19 15:26' select bdate,edate,
出勤分钟=datediff(mi,bdate,edate),
出勤小时=((datediff(mi,bdate,edate)+30)/30)*0.5 from tb/*
bdate edate 出勤分钟 出勤小时
-------------------------------------------------------------
2008-04-19 14:00:00.000 2008-04-19 14:02:00.000 2 .5
2008-04-19 14:00:00.000 2008-04-19 14:15:00.000 15 .5
2008-04-19 14:00:00.000 2008-04-19 14:35:00.000 35 1.0
2008-04-19 14:00:00.000 2008-04-19 15:26:00.000 86 1.5*/drop table tb
---------------------------------------------
是啊,如果出勤这样算该有多好啊~ 改正一下,哈哈!
create table tb(bdate datetime,edate datetime)
insert into tb
select '2008-04-19 14:00','2008-04-19 14:02' union all
select '2008-04-19 14:00','2008-04-19 14:15' union all
select '2008-04-19 14:00','2008-04-19 14:35' union all
select '2008-04-19 14:00','2008-04-19 15:26' select bdate,edate,
缺勤分钟=datediff(mi,bdate,edate),
缺勤小时=((datediff(mi,bdate,edate)+30)/30)*0.5 from tb/*
bdate edate 缺勤分钟 缺勤小时
-------------------------------------------------------------
2008-04-19 14:00:00.000 2008-04-19 14:02:00.000 2 .5
2008-04-19 14:00:00.000 2008-04-19 14:15:00.000 15 .5
2008-04-19 14:00:00.000 2008-04-19 14:35:00.000 35 1.0
2008-04-19 14:00:00.000 2008-04-19 15:26:00.000 86 1.5*/drop table tb