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

Question

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

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

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

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

Answer 1

明天有时间给你写个脚本

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

;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

原表数据：

结果数据：