nxybcce 2017-03-11 03:28 采纳率: 100%
浏览 3979
已采纳

求一考勤统计实现SQL代码

目前有两张表如下

 --用户当日班次表
IF OBJECT_ID('[userKQ]') IS NOT NULL
    DROP TABLE userKQ

CREATE TABLE userKQ
(
dateKQ VARCHAR (10),  --日期
userid VARCHAR(20),   --员工ID
banci VARCHAR(20),    --班次名称
StartTime DATETIME,   --上班时间
EndTime DATETIME,     --下班时间
StartTime1 DATETIME,  --上班有效打卡时间开始
StartTime2 DATETIME,  --上班有效打卡时间结束
EndTime1 DATETIME,    --下班有效打卡时间开始
EndTime2 DATETIME     --下班有效打卡时间结束
)

INSERT INTO userKQ
SELECT '2017-03-02','000015','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL
SELECT '2017-03-02','000015','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL
SELECT '2017-03-02','000002','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL
SELECT '2017-03-02','000002','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL
SELECT '2017-03-02','000003','上下午','1900-01-01 09:00:00.000','1900-01-01 17:00:00.000','1900-01-01 08:30:00.000','1900-01-01 09:30:00.000','1900-01-01 16:30:00.000','1900-01-01 17:30:00.000' UNION ALL

SELECT '2017-03-03','000015','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL
SELECT '2017-03-03','000015','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL
SELECT '2017-03-03','000002','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL
SELECT '2017-03-03','000002','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL
SELECT '2017-03-03','000003','上下午','1900-01-01 09:00:00.000','1900-01-01 17:00:00.000','1900-01-01 08:30:00.000','1900-01-01 09:30:00.000','1900-01-01 16:30:00.000','1900-01-01 17:30:00.000'


--考勤原始记录表
IF OBJECT_ID('[checkinout]') IS NOT NULL
    DROP TABLE checkinout
CREATE TABLE checkinout
(
userid VARCHAR(20),  --员工ID
checktime DATETIME   --打卡时间
)
INSERT INTO checkinout
--
SELECT  '000014','2017-03-02 07:51:00.000' UNION ALL
SELECT  '000015','2017-03-02 07:12:00.000' UNION ALL
SELECT  '000015','2017-03-02 07:33:00.000' UNION ALL
SELECT  '000015','2017-03-02 07:54:00.000' UNION ALL
SELECT  '000015','2017-03-02 12:14:00.000' UNION ALL
SELECT  '000015','2017-03-02 12:34:00.000' UNION ALL
SELECT  '000015','2017-03-02 13:50:00.000' UNION ALL
--SELECT  '000015','2017-03-02 18:01:00.000' UNION ALL

SELECT  '000002','2017-03-02 07:54:00.000' UNION ALL
SELECT  '000002','2017-03-02 12:14:00.000' UNION ALL
--SELECT  '000002','2017-03-02 13:50:00.000' UNION ALL
SELECT  '000002','2017-03-02 18:01:00.000' UNION ALL

SELECT  '000003','2017-03-02 08:30:00.000' UNION ALL
SELECT  '000003','2017-03-02 16:59:00.000' UNION ALL
SELECT  '000003','2017-03-02 17:15:00.000' UNION ALL

--SELECT  '000015','2017-03-02 07:34:00.000' UNION ALL
SELECT  '000015','2017-03-03 12:01:00.000' UNION ALL
SELECT  '000015','2017-03-03 13:34:00.000' UNION ALL
SELECT  '000015','2017-03-03 18:01:00.000' UNION ALL

SELECT  '000002','2017-03-03 07:54:00.000' UNION ALL
SELECT  '000002','2017-03-03 12:14:00.000' UNION ALL
SELECT  '000002','2017-03-03 13:50:00.000' UNION ALL
--SELECT  '000002','2017-03-03 18:01:00.000' UNION ALL

SELECT  '000003','2017-03-03 17:15:00.000'

--在有效打卡时间段内有多次打卡记录的,取第一次打卡记录,没有打卡记录或不在有效打卡记录时间段的不记考勤,标记NULL
--求实现如下效果,CHECKTIME1和CHECKTIME2分别是有效打卡时间,双手奉上仅有的10分,求高手帮忙.

/*
dateKQ      USERID   BANCI   StartTime                  EndTime                        CHECKTIME1                 CHECKTIME2
2017-03-02  000015  上午       1900-01-01 08:00:00.000    1900-01-01 12:00:00.000        2017-03-02 07:33:00.000    2017-03-02 12:14:00.000
2017-03-02  000015  下午       1900-01-01 14:00:00.000    1900-01-01 18:00:00.000        2017-03-02 13:50:00.000    null
2017-03-02  000002  上午       1900-01-01 08:00:00.000    1900-01-01 12:00:00.000        2017-03-02 07:54:00.000    2017-03-02 12:14:00.000
2017-03-02  000002  下午       1900-01-01 14:00:00.000    1900-01-01 18:00:00.000        null                       2017-03-02 18:01:00.000
2017-03-02  000003  上下午    1900-01-01 09:00:00.000    1900-01-01 17:00:00.000        2017-03-02 08:30:00.000    2017-03-02 16:59:00.000
2017-03-03  000015  上午       1900-01-01 08:00:00.000    1900-01-01 12:00:00.000        2017-03-02 07:33:00.000    2017-03-02 12:14:00.000
2017-03-03  000015  下午       1900-01-01 14:00:00.000    1900-01-01 18:00:00.000        2017-03-02 13:50:00.000    null
2017-03-03  000002  上午       1900-01-01 08:00:00.000    1900-01-01 12:00:00.000        2017-03-03 07:54:00.000    2017-03-02 12:14:00.000
2017-03-03  000002  下午       1900-01-01 14:00:00.000    1900-01-01 18:00:00.000        2017-03-03 13:50:00.000    null
2017-03-03  000003  上下午    1900-01-01 09:00:00.000    1900-01-01 17:00:00.000        null                       2017-03-03 17:15:00.000
*/

  • 写回答

8条回答 默认 最新

  • Seeker-Wu 2017-03-11 13:31
    关注

    这个是因为4号员工上午也没有打开下午也没有打卡,这个需要和userKQ再次left join 即可,因为在你的userKQ表中没有对4号员工在3月2号的考勤数据,
    所以你的4号员工的统计考勤中不会有3月2号的记录,只会多一条3月3号全天无打卡的记录;
    对应的sql语句如下:
    SELECT
    u1.dateKQ ,
    u1.userid ,
    u1.banci ,
    u1.StartTime ,
    u1.EndTime ,
    tb2.checktime1 ,
    tb2.checktime2
    FROM
    userKQ u1
    LEFT JOIN(
    SELECT
    a.*, b.checkTime2
    FROM
    (
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime1
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >= time(u.StartTime1)
    AND time(c.checktime) <= time(u.StartTime2)
    )
    ) tmp1
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime
    ) a
    LEFT JOIN(
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime2
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >= time(u.EndTime1)
    AND time(c.checktime) <= time(u.EndTime2)
    )
    ) tmp2
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime
    ) b ON(
    a.dateKQ = b.dateKQ
    AND a.userid = b.userid
    AND a.banci = b.banci
    )
    UNION
    SELECT
    b.dateKQ ,
    b.userid ,
    b.banci ,
    b.StartTime ,
    b.EndTime ,
    a.checktime1 ,
    b.checktime2
    FROM
    (
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime1
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >= time(u.StartTime1)
    AND time(c.checktime) <= time(u.StartTime2)
    )
    ) tmp1
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime
    ) a
    RIGHT JOIN(
    SELECT
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime ,
    min(checktime) AS checktime2
    FROM
    (
    SELECT
    u.*, c.checktime
    FROM
    userKQ u
    JOIN checkinout c ON u.userid = c.userid
    WHERE
    date(c.checktime) = u.dateKQ
    AND(
    time(c.checktime) >= time(u.EndTime1)
    AND time(c.checktime) <= time(u.EndTime2)
    )
    ) tmp2
    GROUP BY
    dateKQ ,
    userid ,
    banci ,
    StartTime ,
    EndTime
    ) b ON(
    a.dateKQ = b.dateKQ
    AND a.userid = b.userid
    AND a.banci = b.banci
    )
    ) tb2 ON(
    u1.dateKQ = tb2.dateKQ
    AND u1.userid = tb2.userid
    AND u1.banci = tb2.banci
    )
    下面为执行结果图:
    图片说明

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(7条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB怎么通过柱坐标变换画开口是圆形的旋转抛物面?
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿