上班打卡数据分析

上班打卡数据

员工表创建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;