Update table data, fetched from another table

I have a table which is storing the attendance information on an employee and another table that’s storing the information about the shift of the employee which is basically a duty roster.

Here is the structure to attendance table

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')

Here is the structure and sample data for Duty roster.

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');

The idea is to update the ShiftCode in Attendance table wrt to the shifts stored in the duty roster. So if the attendance for employee 1 is between '2019-09-01' and '2019-09-05' then the shift code for this employee should be updated to 24 and same for other employee. If the duty roster does not exist for the dates present in attendance table it should not update it and let it the way it is. I need an update query.

Answer

Something like this:

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]
    )

and with update:

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]
    );

Advertisement

Answer