求助拼sql语句，考勤类相关

1.需求是想计算如下表数据，计算出每个人的有效工时，用到的字段列为：

姓名（name）、打卡日期（attendancedate）、打卡时间（attendancetime）、打卡位置(attendancearea)
图片说明

2.关键点在于例如有中间出去吃饭的张三，他一天打了四次卡，06时、12时、13时、17时，怎样做到在数据库计算中，得出有效出工时间，默认上班是没有规定时间的，只是中间出去可能吃午饭会再次刷一次出门卡和一次进门卡，怎么扣除出去的时间，计算得出有效的在公司内的有效工时，例如张三出去吃饭一小时，那这一天的有效工时是不会包含中午吃饭的一小时的，还有一种可能就是中间多刷了一次下班卡，或者多刷了一次上班卡，或者N次，但是没有一个进一个出对应起来的都算异常，怎样将这个标记为异常不计算标记查询出来的数据为异常数据。请高手指教一下。感激不尽

3.图中例子只是参考，会有很多人很多打卡数据的，也会有很多天的，希望可以支持区间多日查询有total有效工时的计算，请给出sql语句，拼出显示数据的t-sql，可以执行并看见效果。可以用临时表计算存储过程，种类不限，可用就行，急急急
效果如图，拼当日计算语句就行了，报表这个按月列出每日的我自己做
图片说明

明天有时间给你写个脚本

今天抽了点时间写这个脚本，有问题可以留言

;WITH temp_table1 AS(
SELECT
     ROW_NUMBER() OVER(ORDER BY ID,attendancedate,attendancetime) num,
     ID,
     name,
     CardNo,
     Dept,
     attendancedate,
     attendancetime,
     attendancearea,
     ROW_NUMBER() OVER(PARTITION BY ID,attendancedate ORDER BY attendancetime) row
FROM temp_table
),mid_table AS(
     SELECT
          ID,
          row,
         CASE WHEN MAX(row) OVER(PARTITION BY ID,attendancedate)%2=0 THEN attendancearea ELSE N'异常' END attendancearea
     FROM temp_table1
)
,temp_table2 AS(
SELECT
     a.ID,
     a.name,
     a.CardNo,
     a.Dept,
     a.attendancedate,
     a.attendancetime in_attendancetime,
     b.attendancetime out_attendancetime,
     a.attendancearea in_attendancearea,
     b.attendancearea out_attendancearea,
     a.row
FROM temp_table1 a
LEFT JOIN temp_table1 b ON a.num=b.num-1 AND b.ID = a.ID
),result_table AS(
SELECT
     temp_table2.*,
     CASE WHEN in_attendancearea=N'门卫入口' 
            THEN DATEDIFF(MI,in_attendancetime,out_attendancetime)
          ELSE DATEDIFF(MI,out_attendancetime,in_attendancetime)
     END work_time
FROM temp_table2
LEFT JOIN mid_table ON mid_table.ID = temp_table2.ID AND mid_table.row = temp_table2.row
WHERE attendancearea=N'异常' OR(out_attendancearea IS NOT NULL AND attendancearea<>N'异常')
)
SELECT
     ID,
     attendancedate,
     ISNULL(RTRIM(CONVERT(DECIMAL(18,2),SUM(work_time)/60.00)),N'异常') work_time
FROM result_table
WHERE ISNULL(work_time,0)>=0
GROUP BY ID,result_table.attendancedate

原表数据：

结果数据：

光用sql去完成这个任务会很复杂。建议：用SQL查询某段时间的数据，再用应用程序去计算，比如你想获得张三1号的时长。
1.用sql获取数据，select * from table where id=001 and attendancedate='2019-05-09' order by attendancetime asc;
2.再用应用程序去做计算。
你说的一种可能会多次打卡的情况，可以这样解决，比如进门打卡1，出去打卡2，出去打卡3。只需计算进门打卡1和进门打卡3的即可。

快下班了，没时间写一个存储过程给你。告诉你我的思路吧。
1. 以你选择的时间区间把这个区间内的所有原始数据抽到临时表中；
2. 以日期为基准循环，例如 2019-05-10 - 2019-05-11，首先 05-10；
3. 先统计 05-10 内有几名员工，使用 group by 筛选原始数据中 05-10 的所有员工，为员工编上行号后存入临时表#tempEmp;
4. 循环 05-10 的员工表，每次只抽取一位员工的 05-10 的打卡记录以打卡时间排序后存入到临时表 #tempAttend，；
5. 如果#tempAttend内记录总数是奇数，则异常，奇数位打卡位置不是门卫入口，偶数位不是门卫出口也是异常；
6. 第5步都没问题，就可以两两计算当日工作时间了。准备一张临时表存放计算结果，异常的数据表中工作时间的那一个为null即可；计算完成即循环到下一个员工。
7. 循环完成 05-10 的所有员工，就进入 05-11，重复进行 3 4 5 6 直至所有日期都循环完成。

其实最简单的就是：规定打卡时间
1.在打卡时间的离上次打卡时间小于半个小时无效>>>记录第一次超过半小时的记录当前时间（解决重复打卡）
2.不再打卡时间打卡>>记录两次打卡时间>>汇总的时候减去这个时间（解决中途离开的情况）
3.如果在打卡时间没有打卡>>而离上次打卡时间超过3小时>>查询是否有请假记录>>a.有：无效打卡时间,b.没有有效打卡时间（解决迟到问题，离下班还有3小时内无效工作时间）
4.如果在打卡时间没有打卡>>且下次打卡没有在下班打卡时间>>查询有没有请假记录>>a.有：效时间, b.没有：无效时间（解决迟到中途请假问题）
5. 构思：在打卡时间》没在打卡时间》请假记录》单独记录数据>>最后汇总处理数据
在打卡时间》设置有效打卡时间断》（第二次打卡与第一次打卡时间没超过这个时间记录第一次打卡，最好设置为上班时间左右）
没在打卡时间》查询是否在打卡时间有打卡记录》有记录为迟到，没有纪录无效打卡时间
6.大门打卡时间》核对是否有请假权限》如果没有》记录时间》如果在打卡时间有正常的打卡就以迟到处理（在规定时间可以出去）》如果没在打卡时间且第二次打卡也没再打卡时间>不做记录（也就是进来了没上班）
7.如果在打卡时间有打卡》核对门禁记录打卡时间》超过某个时间无效打卡时间（卡是打了就是没来上班）