近期在做考勤资料分析时,遇到了一个难题,请各位高手帮忙解决!
公司要求做到的分析为:当一个员工同一天内打卡,在10分钟内存在多笔打卡资料时,只保留最早打卡的那一笔.
提供部分数据供各位测试及分析!
工号 卡机号 上班日期 打卡时间
00000009 09 2009/03/20 07:33
00000009 09 2009/03/20 07:34
00000009 08 2009/03/20 07:45
00000009 09 2009/03/20 12:38
00000009 09 2009/03/20 12:44
00000009 08 2009/03/20 12:55
00000009 09 2009/03/20 17:13
00000009 07 2009/03/20 17:33
00000009 22 2009/03/21 07:34
00000009 19 2009/03/21 07:40
00000009 25 2009/03/23 07:38
00000009 15 2009/03/27 17:47
00000009 07 2009/03/28 07:32
00000009 22 2009/03/28 12:35
00000009 23 2009/03/30 07:31
公司要求做到的分析为:当一个员工同一天内打卡,在10分钟内存在多笔打卡资料时,只保留最早打卡的那一笔.
提供部分数据供各位测试及分析!
工号 卡机号 上班日期 打卡时间
00000009 09 2009/03/20 07:33
00000009 09 2009/03/20 07:34
00000009 08 2009/03/20 07:45
00000009 09 2009/03/20 12:38
00000009 09 2009/03/20 12:44
00000009 08 2009/03/20 12:55
00000009 09 2009/03/20 17:13
00000009 07 2009/03/20 17:33
00000009 22 2009/03/21 07:34
00000009 19 2009/03/21 07:40
00000009 25 2009/03/23 07:38
00000009 15 2009/03/27 17:47
00000009 07 2009/03/28 07:32
00000009 22 2009/03/28 12:35
00000009 23 2009/03/30 07:31
select * from tab as a
where not exist (select * from tab where to_date(上班日期||打卡时间,'yyyy/mm/ddhh:mi')
between to_date(a.上班日期||a.打卡时间,'yyyy/mm/ddhh:mi')-10/60/24 and to_date(a.上班日期||a.打卡时间,'yyyy/mm/ddhh:mi'))
SELECT A.* FROM
(
SELECT IC_CARD_ID,IC_CAD_NO,TO_DATE(TIMES,'YYYY-MM-DD HH24:MI:SS') AS TIMES
FROM HR_TEST ORDER BY IC_CARD_ID,TIMES
) A
WHERE NOT EXISTS (SELECT * FROM (
SELECT IC_CARD_ID,IC_CAD_NO,TO_DATE(TIMES,'YYYY-MM-DD HH24:MI:SS') AS TIMES
FROM HR_TEST ORDER BY IC_CARD_ID,TIMES
) B
WHERE B.TIMES BETWEEN TO_DATE(A.TIMES,'YYYY-MM-DD HH24:MI:SS')-10/24/60 AND TO_DATE(A.TIMES,'YYYY-MM-DD HH24:MI:SS'))
SELECT * FROM 表 t
WHERE NOT EXISTS(
SELECT 1 FROM 表 t1 WHERE t1.工号 = t2.工号 AND t.打卡时间 < t1.打卡时间)
(
SELECT IC_CARD_ID,IC_CAD_NO,TIMES
FROM HR_TEST ORDER BY IC_CARD_ID,TIMES
) A
WHERE NOT EXISTS
(SELECT * FROM (
SELECT IC_CARD_ID,IC_CAD_NO,TO_DATE(TIMES,'YYYY-MM-DD HH24:MI:SS') AS TIMES
FROM HR_TEST ORDER BY IC_CARD_ID,TIMES
) B
WHERE A.IC_CARD_ID=B.IC_CARD_ID AND TO_CHAR(B.TIMES,'YYYY-MM-DD HH24:MI:SS')
BETWEEN TO_CHAR(TO_DATE(A.TIMES,'YYYY-MM-DD HH24:MI:SS')-10/24/60,'YYYY-MM-DD HH24:MI:SS')
AND TO_CHAR(TO_DATE(A.TIMES,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') AND A.IC_CARD_ID='00000542'
)
现在这个写法已经写对了,但是会把00000542的资料清掉了哦!
说明在取员工在某一天内某一段时间内(即10分钟内只取一笔资料)还存在问题!
实际上我要的就是同一个员工在同一天内打卡资料,如果在10分钟内存在多笔打卡资料时,只保留最早打卡的那一笔.就像下面的这些数据.
本人也要测试阶段,语法一经验证即结贴.
主要分析函数如下:
SELECT IC_CARD_ID,MAC_ID,TIMES,NEXTTIMES,(NEXTTIMES-TIMES)*1440 SUBVALUES FROM (
SELECT IC_CARD_ID,MAC_ID,TIMES,LEAD(MAC_ID) OVER (PARTITION BY IC_CARD_ID ORDER BY TIMES) NEXTMAC_ID,
LEAD(TIMES) OVER (PARTITION BY IC_CARD_ID ORDER BY TIMES) NEXTTIMES,
LAG(MAC_ID) OVER (PARTITION BY IC_CARD_ID ORDER BY TIMES) PREMAC_ID,
LAG(TIMES) OVER (PARTITION BY IC_CARD_ID ORDER BY TIMES) PRETIMES
FROM HR_WOLF)
工号 机号 打卡时间
00043728 01 2009-3-27 12:30:00
00043728 02 2009-3-27 12:34:00
00043728 03 2009-3-27 12:36:00
00043728 04 2009-3-27 12:56:00
00043728 04 2009-3-27 17:56:00
00043728 04 2009-3-27 17:58:00最后处理结果:
00043728 01 2009-3-27 12:30:00
00043728 04 2009-3-27 12:56:00
00043728 04 2009-3-27 17:56:00
select TIME1 from kqgrjl
order by TIME1--将间隔十分钟以内的打卡数据过滤
fetch next from crCard into @dktime
while (@@fetch_status<>-1)
begin
if dateadd(mi,-10,@dktime)<@oldtime
begin
delete from kqgrj where TIME1=@dktime
end
select @oldtime=@dktime
fetch next from crCard into @dktime
end
close crCard
select gh,kqjh,cqrq
from(select gh,kqjh,cqrq,lag(cqrq,1,0) over (order by gh,cqrq) cqrq1
from (select gh,kqjh,to_date(rq||time,'yyyy/mm/ddhh24:mi') cqrq from table order by gh,rq,time))
where cqrq-cqrq1>=10/(24*60);
from (select IC_CARD_ID,MAC_ID,TIMES,lag(TIMES) over (order by IC_CARD_ID,TIMES) TIMES1
from (select IC_CARD_ID,MAC_ID,TIMES from HR_WOLF order by IC_CARD_ID,TIMES))
where TIMES-TIMES1>=10/(24*60);结果如下:
00043728 04 2009-3-27 12:56:00
00043728 04 2009-3-27 17:56:00丢了一条记录:
00043728 01 2009-3-27 12:30:00
请再测测看哦
select gh,kqjh,cqrq
from(select gh,kqjh,cqrq,lag(cqrq,1,to_date('11111111','yyyymmdd')) over (order by gh,cqrq) cqrq1
from (select gh,kqjh,to_date(rq||time,'yyyy/mm/ddhh24:mi') cqrq from table order by gh,rq,time))
where cqrq-cqrq1>=10/(24*60);
select IC_CARD_ID,MAC_ID,TIMES
from (select IC_CARD_ID,MAC_ID,TIMES,lag(TIMES,1,to_date('11111111','yyyymmdd')) over (order by IC_CARD_ID,TIMES) TIMES1
from (select IC_CARD_ID,MAC_ID,TIMES from HR_WOLF order by IC_CARD_ID,TIMES))
where TIMES-TIMES1>=10/(24*60);00043728 01 2009-3-27 12:30:00
00043728 04 2009-3-27 12:56:00
00043728 04 2009-3-27 17:56:00