weixin_42853935
weixin_42853935
采纳率100%
2019-05-10 16:11 浏览 442

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

1000

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

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

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

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答 默认 最新

  • 已采纳
    l_w610 l_w610 2019-05-10 18:32

    明天有时间给你写个脚本

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

    ;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
    
    

    原表数据:
    图片说明

    结果数据:
    图片说明

    点赞 1 评论 复制链接分享
  • adele_handsome adele_handsome 2019-05-10 17:46

    光用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的即可。

    点赞 评论 复制链接分享
  • m0_37520989 sShi_JC 2019-05-10 18:06

    快下班了,没时间写一个存储过程给你。告诉你我的思路吧。
    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 直至 所有日期都循环完成。

    点赞 评论 复制链接分享
  • qq_33131171 加金开发 2019-05-10 20:18

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

    点赞 评论 复制链接分享

相关推荐