Update table data, fetched from another table

Question

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.

gotqn · Accepted Answer · 2019-10-07 05:38:15Z

1

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

answered Oct 7, 2019 at 5:38

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Doonie Darkoo · Accepted Answer · 2019-10-07 05:45:32Z

0

I have tried this one and it works too:

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

answered Oct 7, 2019 at 5:45

Doonie Darkoo

1,4952 gold badges24 silver badges38 bronze badges

Comments

B.Muthamizhselvi · Accepted Answer · 2019-10-07 05:48:21Z

0

Try this. It will helpful

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)

answered Oct 7, 2019 at 5:48

B.Muthamizhselvi

6424 silver badges13 bronze badges

Collectives™ on Stack Overflow

Update table data, fetched from another table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related