现在考勤记录表KC1
CLOCK_TIME YGNO CDSL CDMON DSBZ DS
打卡时间 工号 迟到时间 扣款 有效标志 统计天数
08-11-1 7:30:22 001
08-11-1 11:55:23 001
08-11-1 14:01:21 001
08-11-1 17:30:22 001
08-11-2 7:30:22 001
08-11-2 11:55:23 001
08-11-2 13:30:21 001公司规定每天打4次卡,
7:30--8:30 有效,8:01--8:30打卡为迟到
11:50--12:30 有效,
上午有两个有效的记录,才能得到0.25+0.25=0.5天(DS字段),DSBZ才能显示113:00--14:30 有效,14:01--14:30打卡为迟到
17:25--18:00 有效,
下午有两个有效的记录,才能得到0.25+0.25=0.5天(DS字段),DSBZ才能显示1迟到罚款按每次迟到时间进行计算,如迟5分钟,扣10元等
CLOCK_TIME YGNO CDSL CDMON DSBZ DS
打卡时间 工号 迟到时间 扣款 有效标志 统计天数
08-11-1 7:30:22 001 1 0.25
08-11-1 11:55:23 001 1 0.25
08-11-1 14:01:21 001 1:21 10 1 0.25
08-11-1 17:30:22 001 1 0.25
08-11-2 7:30:22 001 1 0.25
08-11-2 11:55:23 001 1 0.25
08-11-2 13:30:21 001 0 0
2天打卡7次,有效统计天数时1.5天,请问高手,如果写这个存贮过程?
CLOCK_TIME YGNO CDSL CDMON DSBZ DS
打卡时间 工号 迟到时间 扣款 有效标志 统计天数
08-11-1 7:30:22 001
08-11-1 11:55:23 001
08-11-1 14:01:21 001
08-11-1 17:30:22 001
08-11-2 7:30:22 001
08-11-2 11:55:23 001
08-11-2 13:30:21 001公司规定每天打4次卡,
7:30--8:30 有效,8:01--8:30打卡为迟到
11:50--12:30 有效,
上午有两个有效的记录,才能得到0.25+0.25=0.5天(DS字段),DSBZ才能显示113:00--14:30 有效,14:01--14:30打卡为迟到
17:25--18:00 有效,
下午有两个有效的记录,才能得到0.25+0.25=0.5天(DS字段),DSBZ才能显示1迟到罚款按每次迟到时间进行计算,如迟5分钟,扣10元等
CLOCK_TIME YGNO CDSL CDMON DSBZ DS
打卡时间 工号 迟到时间 扣款 有效标志 统计天数
08-11-1 7:30:22 001 1 0.25
08-11-1 11:55:23 001 1 0.25
08-11-1 14:01:21 001 1:21 10 1 0.25
08-11-1 17:30:22 001 1 0.25
08-11-2 7:30:22 001 1 0.25
08-11-2 11:55:23 001 1 0.25
08-11-2 13:30:21 001 0 0
2天打卡7次,有效统计天数时1.5天,请问高手,如果写这个存贮过程?
select '08-11-1 7:30:22' 打卡时间,'001' 工号 into #t
union all select '08-11-1 11:55:23','001'
union all select '08-11-1 14:01:21','001'
union all select '08-11-1 17:30:22','001'
union all select '08-11-2 7:30:22','001'
union all select '08-11-2 11:55:23','001'
union all select '08-11-2 13:30:21','001'
select 工号,cast(打卡时间 as datetime) 打卡时间,
convert(varchar(10),cast(打卡时间 as datetime),120) 打卡日期
into #t1 from #t--查询select 工号,打卡时间,
-----迟到时间
case when 打卡时间 between cast(打卡日期 +' 08:00:00' as datetime) and cast(打卡日期 +' 08:30:00' as datetime) or
打卡时间 between cast(打卡日期 +' 14:00:00' as datetime) and cast(打卡日期 +' 14:30:00' as datetime)
then cast(datepart(minute,打卡时间) as varchar(2)) +':' +cast(datepart(second,打卡时间) as varchar(2))
else '' end 迟到时间,
-----扣款
case when 打卡时间 between cast(打卡日期 +' 08:00:00' as datetime) and cast(打卡日期 +' 08:30:00' as datetime) or
打卡时间 between cast(打卡日期 +' 14:00:00' as datetime) and cast(打卡日期 +' 14:30:00' as datetime)
then datepart(minute,打卡时间) *10/5
else '' end 扣款,
-----有效标志
case when 打卡时间 between cast(打卡日期 +' 07:30:00' as datetime) and cast(打卡日期 +' 08:30:00' as datetime)
then case when
(select count(1) from #t1 where 打卡时间 between cast(t.打卡日期 +' 11:50:00' as datetime) and
cast(t.打卡日期 +' 12:30:00' as datetime))>0 then 1 else 0 end
when 打卡时间 between cast(打卡日期 +' 11:50:00' as datetime) and cast(打卡日期 +' 12:30:00' as datetime)
then case when
(select count(1) from #t1 where 打卡时间 between cast(t.打卡日期 +' 07:30:00' as datetime) and
cast(t.打卡日期 +' 08:30:00' as datetime))>0 then 1 else 0 end
when 打卡时间 between cast(打卡日期 +' 13:00:00' as datetime) and cast(打卡日期 +' 14:30:00' as datetime)
then case when
(select count(1) from #t1 where 打卡时间 between cast(t.打卡日期 +' 17:25:00' as datetime) and
cast(t.打卡日期 +' 18:00:00' as datetime))>0 then 1 else 0 end
when 打卡时间 between cast(打卡日期 +' 17:25:00' as datetime) and cast(打卡日期 +' 18:00:00' as datetime)
then case when
(select count(1) from #t1 where 打卡时间 between cast(t.打卡日期 +' 13:00:00' as datetime) and
cast(t.打卡日期 +' 14:30:00' as datetime))>0 then 1 else 0 end
else 0 end 有效标志,
-----统计天数
case when 打卡时间 between cast(打卡日期 +' 07:30:00' as datetime) and cast(打卡日期 +' 08:30:00' as datetime)
then case when
(select count(1) from #t1 where 打卡时间 between cast(t.打卡日期 +' 11:50:00' as datetime) and
cast(t.打卡日期 +' 12:30:00' as datetime))>0 then 0.25 else 0 end
when 打卡时间 between cast(打卡日期 +' 11:50:00' as datetime) and cast(打卡日期 +' 12:30:00' as datetime)
then case when
(select count(1) from #t1 where 打卡时间 between cast(t.打卡日期 +' 07:30:00' as datetime) and
cast(t.打卡日期 +' 08:30:00' as datetime))>0 then 0.25 else 0 end
when 打卡时间 between cast(打卡日期 +' 13:00:00' as datetime) and cast(打卡日期 +' 14:30:00' as datetime)
then case when
(select count(1) from #t1 where 打卡时间 between cast(t.打卡日期 +' 17:25:00' as datetime) and
cast(t.打卡日期 +' 18:00:00' as datetime))>0 then 0.25 else 0 end
when 打卡时间 between cast(打卡日期 +' 17:25:00' as datetime) and cast(打卡日期 +' 18:00:00' as datetime)
then case when
(select count(1) from #t1 where 打卡时间 between cast(t.打卡日期 +' 13:00:00' as datetime) and
cast(t.打卡日期 +' 14:30:00' as datetime))>0 then 0.25 else 0 end
else 0 end 统计天数from #t1 tdrop table #t,#t1/*结果
工号 打卡时间 迟到时间 罚款 有效标志 统计天数
---- ----------------------------------------------------- ----- ----------- ----------- -----
001 2008-11-01 07:30:22.000 0 1 .25
001 2008-11-01 11:55:23.000 0 1 .25
001 2008-11-01 14:01:21.000 1:21 2 1 .25
001 2008-11-01 17:30:22.000 0 1 .25
001 2008-11-02 07:30:22.000 0 1 .25
001 2008-11-02 11:55:23.000 0 1 .25
001 2008-11-02 13:30:21.000 0 0 .00
*/