目前有两张表如下
--用户当日班次表
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
*/