上班打卡数据
员工表创建SQL语句脚本
CREATE TABLE "Employee" (
"id" INT NOT NULL,
"emp_code" VARCHAR(50) NOT NULL,
"username" VARCHAR(50) NOT NULL,
"password" VARCHAR(50) NOT NULL,
PRIMARY KEY ("id")
)
;
上班打卡表创建SQL语句脚本
CREATE TABLE "PunchIn" (
"id" INT NOT NULL,
"emp_code" VARCHAR(50) NOT NULL,
"punch_in_time" DATETIME NOT NULL,
"status" VARCHAR(10) NOT NULL,
PRIMARY KEY ("id")
)
;
下班打卡表创建SQL语句脚本(注意⚠️:这里使用PunchOut表就可以了)
CREATE TABLE "PunchOut" (
"id" INT NOT NULL,
"emp_code" VARCHAR(50) NOT NULL,
"punch_out_time" DATETIME NOT NULL,
"status" VARCHAR(10) NOT NULL,
PRIMARY KEY ("id")
)
;
CREATE TABLE "PunchOut_0" (
"id" INT NOT NULL,
"emp_code" VARCHAR(50) NOT NULL,
"punch_out_time" DATETIME NOT NULL,
"status" VARCHAR(10) NOT NULL,
PRIMARY KEY ("id")
)
;
获取最新一条打卡数据不管下班打卡时间是正常还是早退
SELECT a.id,a1.emp_code AS 工号, a1.username AS 用户名,CONVERT(VARCHAR(19), a.punch_in_time, 120) AS 上班打卡时间,
a.status AS 上班打卡状态,CONVERT(VARCHAR(19), b.punch_out_time, 120) AS 下班打卡时间, b.status AS 下班打卡状态
FROM (SELECT * FROM Employee) a1 LEFT JOIN PunchIn a ON a1.emp_code=a.emp_code
LEFT JOIN PunchOut b ON a.emp_code = b.emp_code
AND CONVERT(DATE, a.punch_in_time) = CONVERT(DATE, b.punch_out_time)
AND b.punch_out_time = (
SELECT MAX(punch_out_time) FROM PunchOut
WHERE emp_code = a.emp_code AND CONVERT(DATE, punch_out_time) = CONVERT(DATE, a.punch_in_time)
)
ORDER BY a.emp_code, a.punch_in_time;