CODEWITHSUNDEEP
sqlsql-server
Jorz
Jorz

Reputation: 358

SQL - Swap/Interchange field values

Thanks for all your help! Here's another one...

Situation:

Our company operates from 8AM - 6PM.
We use biometric device for attendance.
Employee will click "IN" or "OUT" first on the device before pressing it's finger to know if it's IN(AM) or OUT(PM).

Problem:

Some employees wrongly click "OUT" on the device and press their fingers as they check IN, registering it as OUT(AM).. and vice versa, IN(PM).

Below is a sample data for reference: 

EmpID    LogTime                  CheckType
1        4/4/2017 7:24:22 AM      OUT
1        4/4/2017 18:02:11 PM     IN

One employee hit "OUT" but the time is considered "IN" early in the morning. And hit "IN" when leaving the office in the afternoon.

Question:

Using SQL(SQL Server), how can I interchange their checktypes?

Thanks a lot guys!

Upvotes: 1

Views: 268

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

My strategy is to assign a row number to each pair of employee records. However, we assign a row number ordering by both the log time and the check type. If the order be correct, then viewing a given employee record pair ascending by date, both row numbers should be in lock step as 1-2. However, if the order be reversed, then we should see 1-2 for the log time, but 21 for the check type.

WITH cte AS (
    SELECT EmpID, LogTime, CheckType,
           ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY LogTime) rn1,
           ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY CheckType) rn2
    FROM yourTable
)
UPDATE cte
SET CheckType = CASE WHEN CheckType = 'OUT' THEN 'IN' ELSE 'OUT' END
WHERE rn1 <> rn2

The following table might show what I have in mind:

EmpID    LogTime                  CheckType    rn1    rn2
1        4/4/2017 7:24:22 AM      OUT          1      2
1        4/4/2017 18:02:11 PM     IN           2      1

If employee 1 had logged his time correctly, we would see this:

EmpID    LogTime                  CheckType    rn1    rn2
1        4/4/2017 7:24:22 AM      OUT          1      1
1        4/4/2017 18:02:11 PM     IN           2      2

In other words, both computed row numbers would be the same.

Here is a demo of the CTE I used with your sample data:

Rextester

Upvotes: 1

Zunair Fatimi
Zunair Fatimi

Reputation: 116

Update attendance set checkType = 'IN' where LogTime between <min range> and <max range>

Give a range for morning time similar is the case with checkout.

Upvotes: 2

Related Questions