Question

SQL语句的编写,查询公司所有普通员工某一天第一次的刷卡时间和最后一次刷卡时间的汇总。

公司是一个大公司，会有好多各种级别的员工，每天刷卡的时间，位置，和次数都不一样。
假设有3种级别，老板，经理，和普通员工。
假设公司共有4个门，东南西北各一个，每个门都只有一个进来的读卡器，出去的时候是按钮。
所以也就是说每次打卡都是为了进来，除了最后一次，那必定是为了记录下班时间而打的。
比如，A最早在东门（east door）打卡纪录开始上班，在南门（south door）打卡记录下班。
这个数据库里面会有好几个月的数据，而不是一天一天的。
（上班时间信息，普通员工上班时断规定为 9：00am - 6：00 pm
早上10点后进来算旷工，其记录就不需要用作计算，9点到10点之间算迟到）

[目的] 查询某一天（比如2013-09-09）所有普通员工的上班（第一次打卡）记录，包括员工姓名，打卡时间，打卡位置，和其下班（最后一次打卡）记录。

情况1：如果有员工忘记下班打卡，也就是跟着别人出去，那么在规定的下班时间之后就不会有这个人的记录，而该员工最后一次的打卡时间就会是在规定下班时间之前，又或者一整天就只有一条进来的记录。

情况2：其情况如第二题，没有下班后的打卡记录，但是有员工是因为加班到第二天早上才打卡出去，但是又不能与其第二天重新进来上班的时间混淆。

总结：如果当天没有下班打卡记录那么就显示第二天的第一条记录。这里要考虑的就是如果该员工在第二天规定上班时间之前有两条打卡的记录，就可适用，但是如果该员工在规定上班时间之后一小时，也就是10点之前只有一条打卡记录的话，前一天就当作早退处理。

希望查询的效果就是如无意外，简单 查询某一天（比如2013-09-09）所有普通员工的上班（第一次打卡）记录，包括员工姓名，打卡时间，打卡位置，和其下班（最后一次打卡）记录。

如有员工这天没有下班时间之后的打卡记录，而其在第二天上班时间之前有2条打卡记录的，就当作加班处理，其查询的下班时间就显示第二天的第一条记录。

情大神帮忙，满意后要多少分多可以！！！现在此谢过了~

Answer 1

感觉只能用游标来做，普通的SQL几乎不能实现。

追问

什么是游标？

追答

我现在非常担心，即使我写出了完整的SQL，你如果看不太懂SQL的话，那么也是运行不了的。

这样吧，我把核心思路和你说下吧，有什么问题再后续讨论。
因为，你的需求比较复杂，我是用存储过程来实现，在这个存储过程里面，有临时表和游标技术。
下面说下步骤：
假设数据库原始数据为：
姓名 打卡时间
张三 2013-09-09 09：01：00
张三 2013-09-09 18：00：02
李四 2013-09-09 08：50：00
王五 2013-09-09 10：50：00
王五 2013-09-09 18：51：00
赵六 2013-09-09 09：50：00
赵六 2013-09-09 16：30：00

step1：整理，得出每个人的上班时间和下班时间,放在临时表tempBeginEnd里,存储过程接受一个参数日期,记为@queryDate

select name,max(swipingTime) as 'endTime' ,min(swipingTime) as 'beginTime' into tempBeginEnd from t_records group by name where swipingTime=@queryDate

step2:再对临时表tempBeginEnd进行处理,

在对上班时间的处理中，如果是9点之前的，则是正常上班
9到10点的视为迟到
10点以后到的视为旷工
SQL语句如下：
select case when beginTime09:00:00 and beginTime10:00:00 then '旷工' end
as '上班考勤',
case when endTime<18:00:00 then '早退' end as '下班考勤'
into temp
from tempBeginEnd

我现在非常担心，即使我写出了完整的SQL，你如果看不太懂SQL的话，那么也是运行不了的。

这样吧，我把核心思路和你说下吧，有什么问题再后续讨论。

因为，你的需求比较复杂，我是用存储过程来实现，在这个存储过程里面，有临时表和游标技术。

下面说下步骤：

假设数据库原始数据为：

姓名         打卡时间
张三           2013-09-09 09：01：00
李四           2013-09-09 08：50：00
王五           2013-09-09 18：51：00
赵六           2013-09-09 16：30：00

step1：整理，得出每个人的上班时间和下班时间,放在临时表tempBeginEnd里,存储过程接受一个参数日期,记为@queryDate

select name,max(swipingTime) as 'endTime' ,min(swipingTime) as 'beginTime' into tempBeginEnd from t_records group by name where swipingTime=@queryDate

step2:再对临时表tempBeginEnd进行处理,

SQL语句如下：

select case when beginTime<09:00:00 then '正常上班'
when beginTime>09:00:00 and beginTime<10:00:00 then '迟到'
when beginTime>10:00:00 then '旷工' end 
as '上班考勤',
case when endTime<18:00:00 then '早退' end as '下班考勤'
into temp
from tempBeginEnd

step3:但是现在的temp表目前还不能满足需求，要考虑加班

此时要用到游标去遍历，去查询次日早上在9:00之前的刷卡次数是否大于2次，

如果大于2次，则将早退更新成加班

select @folloingCount=count(*) from temp where swipingTime=次日 and swipingTime<10:00:00
if @followingCount>=2 update temp set 下班考勤='加班'

追问

看到你的回答,很用心,真的很感谢,我等的就是这样的回答.
但是突然我想到另外一种状况,其实这就是我被要求要解决的最重要问题,那就是如果有人加班到隔天,如何知道呢?
我突然发现我设想的上班时间打两次卡有漏洞,那就是要是有人去了趟厕所又回来也可以构成两条或多过两条的打卡记录.
我在学校学的真的很有限,全是基本知识,现在要我全权负责这么个庞大的考勤系统,我有点hold不住...

追答

是啊，你之前的在第二天判断是否加班的方法是不太准确的。
我帮你想想，看看有没有好的思路。

追问

能给个Q号不? 你可以发到我邮箱
ASK_JX(小老鼠)hotmail.com
[这里不让发邮箱...小老鼠就是@]
再多分我都可以给,就是想快点解决问题,然后有问题的时候可以找到人讨论.
谢谢了

追答

是这样的，我想了一下，
我觉得必须要人为规定当天如果加班，
必须在23:59左右打卡，而且在第二天00：01左右再打卡一次，
否则无法实现需求。

为了更好的说明情况，我举例说明：
张三早上正常上班，结果4点早退，第二天早上8点上班。
李四早上正常上班，结果加班到第二天早上8点刷第一次打卡出门买早饭。
王五早上正常上班，结果下班忘记刷卡，最后刷卡时间为17：00，第二天早上8点上班。
那请问这种情况，我如何区分谁是早退？谁是加班？谁是忘记刷卡？

根本无法区分，所以必须人为制定刷卡策略：
1、如下班忘记刷卡，则记为早退。也许有人觉得不满，但是后果也是它自己造成的，怨不得别人。
2、如果加班，必须在当前23：59分前刷卡，以及在次日的0：01左右再打卡，这样在统计时就会大大轻松了。
-------------------------------------------------------------------------------------------------------
tip:在我们公司，有些人早上迟到就不刷卡，在查到时他们就谎称忘刷了，但是我们领导不管，如果漏刷卡，一律视为迟到处理。
所以说，刷卡这东西不能完全依靠系统来做，也要用一些规则来辅助计算。

追问

这个办法不错,就在当天晚上11-12点之间要打卡,隔天0点-1点之间再打卡
虽然对员工来讲会不方便,不过一个小时的限定时间应该ok.,但是第二天的下班打卡时间没办法确定
假如隔天没上班ok,但隔天要有上班,在八点之前很难算
八点之后来也不知道到底是迟到还是下班的时间
所以我这么想,如果是隔夜下班的话,要求员工在走的时候连续成功打卡5次
时间可以规定在一分钟之内,这样应该可以避免掉上厕所,或者是临时误按吧?

追答

我想了很久，我个人觉得考勤系统只适用用来判断某人上班是否迟到，是否早退，用来计算加班时间，不准确而且难度太大到没有意义去做这件事。
依然举几个小例子：
张三跨夜加班：0：01刷卡，4点50回家，中午12点过来刷卡上班，18点正常下班。
李四未跨夜加班：0：01刷卡上班，4点50刷卡出去买早饭，中午12点出去吃饭，18点正常下班。
------------------------------------------------------------------------------------
对于上述例子，请问你用SQL能实现张三，李四加班时间吗？
张三实际上偷懒了，4点50分就回家了，中午12过来，已经迟到了3小时
而李四却勤勤恳恳的从4点50一直干到下班。
-----------------------------------------------------------------------------------
再举一个极端的例子，王五早上9：00刷了一下卡，然后就出去上网吧打游戏了，然后18：00时又回来刷了一下卡，你能说王五今天一天考勤正常，上了8小时班吗？
所以说，门禁考勤这个东西不是太完美，一般情况下，用于判断某人上班是否迟到，是否早退就OK了，至于说判断加班时间等，结合人工处理流程吧，因为加班之类的不是都有申请流程的吗？不可能只凭考勤就断定一个人是否加班了吧？
-----------------------------------------------------------------------------------
最终解决方案：
采取天为唯一结算单位，不允许跨天计算。
如果加班，必须在当前0点前刷卡，以及在次日0点后刷卡。
取某人刷卡的最小时间和最大时间，
最小时间如果小于9，则算正常上班，
最小时间如果大于9且小于10，算迟到，
最小时间如果大于10，则算旷工。
最大时间如果小于18，则算早退
最大时间如果大于18，则算正常下班。

这个里面是有一些误差的（比如上面张三的例子），不过纵观所有员工的刷卡记录来看，这点误差出现情况较少，是在合理误差范围内的。

Answer 2

那你现在打卡记录的表长什么样子,用的是什么数据库
这个不难,追问里发给我

追问

现在还不清楚，我刚刚开始接触，真的很谢谢你。
现在知道的就是它是一个大公司，各种级别各种权限都有，暂时我需要做的就是拿出普通级别员工的上下班的时间，如题所示。
还要清除event log。
就先当作员工信息一个表，表里面就是些基本的信息；
读卡器的门一共四个，每个门的打卡记录一个表，
还有一个表，装有员工卡的信息。

数据库用的是SQL2008 R2

追答

这个你又没有具体的情况,而且你自己也不明确要做成什么样子
这让人很难介入啊
理论上来说,通过sql语句很容易实现改需求,
对每一个员工每一天分组之后通过case函数计算它的标记值可以看出是否迟到

追问

而且现在我拿到的数据库bak文件是Honey Well公司的ProWatch系统结合打卡器生成的.
它不是直接通过数据库来设置考勤系统,而是用ProWatch替客户量身定做比如好几个门的读卡器.
现在老板就是希望自己做考勤系统,我上网查了很多资料,一个人不太可能办成,要考虑的点太多了.
更何况我还是个彻头彻尾的新人...