更新表数据，从另一个表中获取

Question

我有一张表，用来存储员工的出勤信息，另一张表，存储的是关于员工轮班的信息，基本上是一份值班名册。

这是考勤表的结构。

CREATE TABLE Attendance
(
ID INT,
EmpCode INT,
ShiftCode INT, 
CheckIn DATETIME,
CheckOut DATETIME
)



INSERT INTO Attendance VALUES (1, 1, 1, '2019-09-01 09:16:23', NULL)
INSERT INTO Attendance VALUES (2, 1, 1, NULL, '2019-09-01 18:01:56')
INSERT INTO Attendance VALUES (3, 1, 2, '2019-09-02 09:00:00', NULL)
INSERT INTO Attendance VALUES (4, 1, 2, NULL, '2019-09-02 18:48:21')
INSERT INTO Attendance VALUES (5, 1, 1, '2019-09-13 09:27:00', NULL)
INSERT INTO Attendance VALUES (6, 1, 1, NULL, '2019-09-13 18:45:00')
INSERT INTO Attendance VALUES (7, 2, 2, '2019-09-01 21:19:17', NULL)
INSERT INTO Attendance VALUES (8, 2, 2, NULL, '2019-09-01 23:30:56')
INSERT INTO Attendance VALUES (9, 2, 2, '2019-09-05 09:23:00', NULL)
INSERT INTO Attendance VALUES (10, 2, 2, NULL, '2019-09-05 17:19:00')

这是职务名册的结构和样本数据。

CREATE TABLE Shifts
(
ID INT PRIMARY KEY,
EmpCode INT,
ShiftCode INT,
StartDate DATETIME,
EndDate DATETIME
)

INSERT INTO Shifts VALUES (1, 1, 24, '2019-09-01 00:00:00', '2019-09-05 00:00:00');
INSERT INTO Shifts VALUES (2, 2, 25, '2019-09-01 00:00:00', '2019-09-05 00:00:00');

这样做的目的是将考勤表中的ShiftCode更新为值班名册中存储的班次。因此，如果employee 1的考勤在'2019-09-01'和'2019-09-05'之间，则应将该员工的轮班代码更新为24，而对于其他员工则是相同的。如果值勤表中的日期不存在工作人员名册，就不应对名册进行更新，让名册保持原样。我需要一个更新查询。

Answer 1

就像这样：

SELECT *
FROM Attendance A
INNER JOIN Shifts S
    ON A.EmpCode = S.[EmpCode]
    AND 
    (
        A.CheckIn BETWEEN S.[StartDate] AND S.[EndDate]
        OR
        A.CheckOut BETWEEN S.[StartDate] AND S.[EndDate]
    )

并提供最新资料：

UPDATE Attendance
SET ShiftCode = S.[ShiftCode]
FROM Attendance A
INNER JOIN Shifts S
    ON A.EmpCode = S.[EmpCode]
    AND 
    (
        A.CheckIn BETWEEN S.[StartDate] AND S.[EndDate]
        OR
        A.CheckOut BETWEEN S.[StartDate] AND S.[EndDate]
    );

Answer 2

我试过这个，它也起作用了：

UPDATE Attendance
SET ShiftCode = ISNULL((SELECT ShiftCode FROM Shifts Roster
WHERE CAST(COALESCE(CheckIn, CheckOut) AS DATE) BETWEEN StartDate AND EndDate AND EmpCode = Attendance.EmpCode),
(SELECT ShiftCode FROM EmployeeInfo WHERE EmployeeInfo.ID = Attendance.EmpCode))

Answer 3

尝尝这个。会有帮助的

UPDATE Attendance SET ShiftCode=c.ShiftsShiftCode
FROM Attendance a
JOIN
(
    SELECT a.EmpCode, a.ShiftCode, CheckIn, CheckOut, b.ShiftCode AS ShiftsShiftCode FROM Attendance a
    JOIN Shifts b ON a.EmpCode=b.EmpCode
    AND (a.CheckIn BETWEEN StartDate AND EndDate OR a.CheckOut BETWEEN StartDate AND EndDate)
)c
ON a.EmpCode = c.EmpCode
AND (a.checkin=c.checkin OR a.CheckOut=c.CheckOut)