如何查询代打卡嫌疑人清单

sun8330 2012-06-18 03:07:29
考勤系统原始打卡记录表结构如下:
WORKRECO (WKR_INCOD char(7) NOT NULL,--工号
WKR_DATTIM smalldatetime NOT NULL, --刷卡时间
WKR_CACS varchar(3) NOT NULL) --刷卡机器号

问题：如何查询两天以上(含两天)，刷卡时间与刷卡机器号相同的员工,显示结果最好有如下栏位:

工号１,工号２，起始日期，截止日期
...全文
435 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
sun8330 2012-06-19
  • 打赏
  • 举报
 回复
上面两位高手，我用的是sql2000,有些指令用不了，我还在测试，如果方便的话，能否改成sql2000的指令，方便我测试一下，谢谢!
开着拖拉机泡妞 2012-06-18
  • 打赏
  • 举报
 回复 


--> 测试数据：[WORKRECO]

if object_id('[WORKRECO]') is not null 

drop table [WORKRECO]

go

create table [WORKRECO](

[WKR_INCOD] varchar(5),

[WKR_DATTIM] datetime,

[WKR_CACS] varchar(3)

)

go

insert [WORKRECO]

select 'B0001','2012-06-01 07:58:00','001' union all

select 'B0002','2012-06-01 07:58:00','001' union all

select 'B0003','2012-06-01 07:50:00','001' union all

select 'B0001','2012-06-01 12:01:00','002' union all

select 'B0002','2012-06-01 12:01:00','002' union all

select 'B0003','2012-06-01 12:03:00','002' union all

select 'B0001','2012-06-01 13:29:00','002' union all

select 'B0002','2012-06-01 13:29:00','002' union all

select 'B0003','2012-06-01 13:28:00','001' union all

select 'B0001','2012-06-01 17:35:00','001' union all

select 'B0002','2012-06-01 17:35:00','001' union all

select 'B0003','2012-06-01 17:30:00','001' union all

select 'B0001','2012-06-02 07:58:00','001' union all

select 'B0002','2012-06-02 07:58:00','001' union all

select 'B0003','2012-06-02 07:50:00','001' union all

select 'B0001','2012-06-02 12:01:00','002' union all

select 'B0002','2012-06-02 12:01:00','002' union all

select 'B0003','2012-06-02 12:03:00','002' union all

select 'B0001','2012-06-02 13:29:00','002' union all

select 'B0002','2012-06-02 13:29:00','002' union all

select 'B0003','2012-06-02 13:28:00','001' union all

select 'B0001','2012-06-02 17:35:00','001' union all

select 'B0002','2012-06-02 17:35:00','001' union all

select 'B0003','2012-06-02 17:30:00','001' union all

select 'B0001','2012-06-03 07:58:00','001' union all

select 'B0002','2012-06-03 07:58:00','001' union all

select 'B0003','2012-06-03 07:50:00','001' union all

select 'B0001','2012-06-03 12:01:00','002' union all

select 'B0002','2012-06-03 12:01:00','002' union all

select 'B0003','2012-06-03 12:03:00','002' union all

select 'B0001','2012-06-03 13:29:00','002' union all

select 'B0002','2012-06-03 13:29:00','002' union all

select 'B0003','2012-06-03 13:28:00','001' union all

select 'B0001','2012-06-03 17:35:00','001' union all

select 'B0002','2012-06-03 17:35:00','001' union all

select 'B0003','2012-06-03 17:30:00','001'

go



--问题：如何查询两天以上(含两天)，刷卡时间与刷卡机器号相同的员工,显示结果最好有如下栏位

;with t

as(

select 

    px=ROW_NUMBER()over(partition by [WKR_INCOD] order by [WKR_DATTIM]),

    de=convert(varchar(10),dateadd(dd,-DENSE_RANK()over(partition by [WKR_INCOD] order by convert(varchar(10),[WKR_DATTIM],120)),[WKR_DATTIM]),120),

    [WKR_INCOD],

    [WKR_DATTIM],

    [WKR_CACS]

from 

    [WORKRECO]

)

select distinct  

    case when a.WKR_INCOD<b.WKR_INCOD then a.WKR_INCOD else b.WKR_INCOD end as 工号一,

    case when a.WKR_INCOD>b.WKR_INCOD then a.WKR_INCOD else b.WKR_INCOD end as 工号二, 

    convert(varchar(10),MIN(a.WKR_DATTIM),120) as 起始时间,

    convert(varchar(10),MAX(b.WKR_DATTIM),120) as 结束时间

from 

    t a,t b 

where 

    a.de=b.de 

    and a.px=b.px 

    and a.WKR_CACS=b.WKR_CACS 

    and a.WKR_INCOD<>b.WKR_INCOD 

    and a.WKR_DATTIM=b.WKR_DATTIM

group by 

    a.WKR_INCOD,b.WKR_INCOD,a.de

having

    DATEDIFF(DD,MIN(a.WKR_DATTIM),MAX(b.WKR_DATTIM))>=2

/*

工号一	工号二	起始时间	结束时间

------------------------------------------------

B0001	B0002	2012-06-01	2012-06-03

*/
孤独加百列 2012-06-18
  • 打赏
  • 举报
 回复
写死我了，希望你看得懂。。。


IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'WORKRECO')

BEGIN

	DROP TABLE WORKRECO

END

GO

CREATE TABLE WORKRECO 

(

	WKR_INCOD char(7) NOT NULL,--工号

	WKR_DATTIM smalldatetime NOT NULL, --刷卡时间

	WKR_CACS varchar(3) NOT NULL

) --刷卡机器号

GO

INSERT INTO WORKRECO

SELECT 'B0001','2012-06-01 07:58:00','001' UNION

SELECT 'B0002','2012-06-01 07:58:00','001' UNION

SELECT 'B0003','2012-06-01 07:50:00','001' UNION

SELECT 'B0001','2012-06-01 12:01:00','002' UNION

SELECT 'B0002','2012-06-01 12:01:00','002' UNION

SELECT 'B0003','2012-06-01 12:03:00','002' UNION

SELECT 'B0001','2012-06-01 13:29:00','002' UNION

SELECT 'B0002','2012-06-01 13:29:00','002' UNION

SELECT 'B0003','2012-06-01 13:28:00','001' UNION

SELECT 'B0001','2012-06-01 17:35:00','001' UNION

SELECT 'B0002','2012-06-01 17:35:00','001' UNION

SELECT 'B0003','2012-06-01 17:30:00','001' UNION



SELECT 'B0001','2012-06-02 07:58:00','001' UNION

SELECT 'B0002','2012-06-02 07:58:00','001' UNION

SELECT 'B0003','2012-06-02 07:50:00','001' UNION

SELECT 'B0001','2012-06-02 12:01:00','002' UNION

SELECT 'B0002','2012-06-02 12:01:00','002' UNION

SELECT 'B0003','2012-06-02 12:03:00','002' UNION

SELECT 'B0001','2012-06-02 13:29:00','002' UNION

SELECT 'B0002','2012-06-02 13:29:00','002' UNION

SELECT 'B0003','2012-06-02 13:28:00','001' UNION

SELECT 'B0001','2012-06-02 17:35:00','001' UNION

SELECT 'B0002','2012-06-02 17:35:00','001' UNION

SELECT 'B0003','2012-06-02 17:30:00','001' UNION





SELECT 'B0001','2012-06-03 07:58:00','001' UNION

SELECT 'B0002','2012-06-03 07:58:00','001' UNION

SELECT 'B0003','2012-06-03 07:50:00','001' UNION

SELECT 'B0001','2012-06-03 12:01:00','002' UNION

SELECT 'B0002','2012-06-03 12:01:00','002' UNION

SELECT 'B0003','2012-06-03 12:03:00','002' UNION

SELECT 'B0001','2012-06-03 13:29:00','002' UNION

SELECT 'B0002','2012-06-03 13:29:00','002' UNION

SELECT 'B0003','2012-06-03 13:28:00','001' UNION

SELECT 'B0001','2012-06-03 17:35:00','001' UNION

SELECT 'B0002','2012-06-03 17:35:00','001' UNION

SELECT 'B0003','2012-06-03 17:30:00','001'

GO



WITH t AS

(SELECT WKR_INCOD,CONVERT(VARCHAR(10),WKR_DATTIM,23) AS date,COUNT(1) AS num

FROM WORKRECO AS A

WHERE EXISTS (SELECT 1 FROM WORKRECO AS B WHERE A.WKR_INCOD <> B.WKR_INCOD AND A.WKR_DATTIM = B.WKR_DATTIM AND A.WKR_CACS = B.WKR_CACS)

GROUP BY WKR_INCOD,CONVERT(VARCHAR(10),WKR_DATTIM,23)),

m AS

(

SELECT WKR_INCOD,CONVERT(VARCHAR(10),WKR_DATTIM,23) AS Date,COUNT(1) AS num

FROM WORKRECO AS A

GROUP BY WKR_INCOD,CONVERT(VARCHAR(10),WKR_DATTIM,23)),

n AS

(

SELECT A.WKR_INCOD AS AWKR_INCOD,B.WKR_INCOD AS BWKR_INCOD,A.date

FROM t AS A,m AS B

WHERE A.WKR_INCOD <> B.WKR_INCOD AND A.num = B.num AND A.date = B.Date AND B.WKR_INCOD IN (SELECT WKR_INCOD FROM t WHERE num = B.num AND t.date = B.Date)

),

x AS

(

SELECT AWKR_INCOD,BWKR_INCOD,MIN(date) AS BEGINDATE,MAX(date) AS ENDDATE,COUNT(1) AS num

FROM n

GROUP BY AWKR_INCOD,BWKR_INCOD

HAVING COUNT(1) > 1 AND DATEDIFF(DAY,MIN(date),MAX(date)) + 1 = COUNT(1)

)



SELECT DISTINCT CASE WHEN AWKR_INCOD < BWKR_INCOD THEN AWKR_INCOD

       ELSE BWKR_INCOD END AS AWKR_INCOD,

       CASE WHEN AWKR_INCOD < BWKR_INCOD THEN BWKR_INCOD

       ELSE AWKR_INCOD END AS BWKR_INCOD,BEGINDATE,ENDDATE

FROM x



AWKR_INCOD	BWKR_INCOD	BEGINDATE	ENDDATE

B0001  	B0002  	2012-06-01	2012-06-03

应该中间步骤可以合并的，酌情处理下吧。。。
sun8330 2012-06-18
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 的回复:]
假如有三个人是一样的你又怎么处理？？？
工号１,工号２，起始日期，截止日期
那么三哥一样的时候还有一个工号怎么办？？？？
[/Quote]

你这个问题问得很有道理，我也想过，如果能用一个栏位的字符串也很好，这样就没有这个问题了，如果能输出这样的结果，也算ok,同样会给分，谢谢!
开着拖拉机泡妞 2012-06-18
  • 打赏
  • 举报
 回复
假如有三个人是一样的你又怎么处理？？？
工号１,工号２，起始日期，截止日期
那么三哥一样的时候还有一个工号怎么办？？？？
sun8330 2012-06-18
  • 打赏
  • 举报
 回复
考勤系统原始打卡记录表WORKRECO结构如下:
表名:WORKRECO
栏位说明:(WKR_INCOD char(7) NOT NULL,--工号
WKR_DATTIM smalldatetime NOT NULL, --刷卡时间
WKR_CACS varchar(3) NOT NULL) --刷卡机器号

问题：如何查询连续两天以上(含两天)，刷卡时间与刷卡机器号相同的员工,显示结果最好有如下栏位:

工号１,工号２，起始日期，截止日期

测试数据:
B0001,2012-06-01 07:58:00,001
B0002,2012-06-01 07:58:00,001
B0003,2012-06-01 07:50:00,001
B0001,2012-06-01 12:01:00,002
B0002,2012-06-01 12:01:00,002
B0003,2012-06-01 12:03:00,002
B0001,2012-06-01 13:29:00,002
B0002,2012-06-01 13:29:00,002
B0003,2012-06-01 13:28:00,001
B0001,2012-06-01 17:35:00,001
B0002,2012-06-01 17:35:00,001
B0003,2012-06-01 17:30:00,001

B0001,2012-06-02 07:58:00,001
B0002,2012-06-02 07:58:00,001
B0003,2012-06-02 07:50:00,001
B0001,2012-06-02 12:01:00,002
B0002,2012-06-02 12:01:00,002
B0003,2012-06-02 12:03:00,002
B0001,2012-06-02 13:29:00,002
B0002,2012-06-02 13:29:00,002
B0003,2012-06-02 13:28:00,001
B0001,2012-06-02 17:35:00,001
B0002,2012-06-02 17:35:00,001
B0003,2012-06-02 17:30:00,001


B0001,2012-06-03 07:58:00,001
B0002,2012-06-03 07:58:00,001
B0003,2012-06-03 07:50:00,001
B0001,2012-06-03 12:01:00,002
B0002,2012-06-03 12:01:00,002
B0003,2012-06-03 12:03:00,002
B0001,2012-06-03 13:29:00,002
B0002,2012-06-03 13:29:00,002
B0003,2012-06-03 13:28:00,001
B0001,2012-06-03 17:35:00,001
B0002,2012-06-03 17:35:00,001
B0003,2012-06-03 17:30:00,001


记住，一定要连续，如果一天有一次不相同就不要抓

最后的查询结果应该是:
B0001 B0002 2012-06-01 2012-06-03
表示B0001与B0002在6/1号与6/3号三天的打卡一样

sun8330 2012-06-18
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]
SQL code


select
*
from
tb t
where
exists(select 1 from tb where workeco<>t.workeco and WKR_CACS=t.WKR_CACS and datediff(dd,WKR_DATTIM,tWKR_DATTIM)>=2) and convert(varchar(10),WKR_DATTIM……
[/Quote]
逻辑明显不对，是要查询连续两天以上的打卡完全一样的员工，不是相差两天
--小F-- 2012-06-18
  • 打赏
  • 举报
 回复 
select

 * 

from

 tb t 

where

 exists(select 1 from tb where workeco<>t.workeco and WKR_CACS=t.WKR_CACS  and convert(varchar(10),WKR_DATTIM,108)=convert(varchar(10),t.WKR_DATTIM,108)

and

 WKR_CACS in(select WKR_CACS from tb group by WKR_CACS having count(1)>1)
--小F-- 2012-06-18
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]
SQL code

select
*
from
tb t
where
exists(select 1 from tb where workeco<>t.workeco and WKR_CACS=t.WKR_CACS and datediff(dd,WKR_DATTIM,tWKR_DATTIM)>=2) and convert(varchar(10),WKR_DATTIM,1……
[/Quote]

我错误的理解了两天以上。
开着拖拉机泡妞 2012-06-18
  • 打赏
  • 举报
 回复
给测试数据
昵称被占用了 2012-06-18
  • 打赏
  • 举报
 回复 
SELECT A.WORKRECO , B.WORKRECO,MIN (A.WKR_DATTIM),MAX(A.WKR_DATTIM)

FROM 考勤系统原始打卡记录表 A,考勤系统原始打卡记录表 B

WHERE A.WKR_DATTIM = B.WKR_DATTIM

AND A.WKR_CACS  = B.WKR_CACS 

AND A.WORKRECO < B.WORKRECO

GROUP BY A.WORKRECO , B.WORKRECO

HAVING COUNT(1) > 1
--小F-- 2012-06-18
  • 打赏
  • 举报
 回复 


select

 * 

from

 tb t 

where

 exists(select 1 from tb where workeco<>t.workeco and WKR_CACS=t.WKR_CACS and datediff(dd,WKR_DATTIM,tWKR_DATTIM)>=2) and convert(varchar(10),WKR_DATTIM,108)=convert(varchar(10),t.WKR_DATTIM,108)
AI人工智能毕业设计课题：人脸识别公司签到系统
本周打卡统计：显示本周所有员工的打卡情况 本月打卡统计：显示本月所有员工的打卡情况 本年打卡统计：显示本年所有员工的打卡情况   修改密码 退出系统功能     【员工功能】 录入人脸信息：通过...
SCP收容物131~140
注 ：此文接SCP收容物121~130,本文只供开玩笑,与steve_gqq_MC合作。 --------------------------------------------------------------------------------------------------------------------------------- 目录 scp-131 scp-132 scp-133 scp-134 scp-135 scp-136 scp-137 scp-138 sc
新员工之间的差距
一、进入新公司前的“准备工作” 01 住到公司附近 住的远了，浪费的不仅是时间，还有宝贵的精力。而且，由于新工作是自己真正想做的，住到公司附近，还能让你不用顾及末班车，必要的时候全身心投入工作。有的同事可能要花1个多小时回家，而你5分钟后就能洗澡，并立刻进入梦乡。完全避开了拥挤的交通和黑压压的人群。 02 自我介绍时，不要说“我还什么都不懂” 说自己什么都不懂，这话只能露怯，与谦虚无关...
开发者如何写好技术简历？
戳蓝字“CSDN云计算”关注我们哦！技术头条：干货、简洁、多维全面。更多云计算精华知识尽在眼前，get要点、solve难题，统统不在话下！文章来源一位实验室朋友的投稿，斩...
机器学习项目简历收集册-----机器学习（仅供参考）
图像识别 项目一：齿轮表面粗糙度自动检测 开发应用：python3+sklearn+opencv 项目描述：1）使用CCD相机获取齿轮表面图像 2）图片预处理，使用中值滤波，去除图片椒盐噪声，使用直方图均衡化进行图像增强 3) 使用小波变换提取零件的纹理特征 4）进行pca主成分分析进行降维获取特征 5）使用支持向量机分类器进行分类 项目二：验证码识别 开发应用：python+tensorflow 项目描述：1）通过公司提供的数据集对数据进行编码 2）搭建cnn神经网络 3）对模型进行训练，提高验证码的识别
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章