CODEWITHSUNDEEP
sqlsql-server
Memo
Memo

Reputation: 92

How to filter unique records depending to some condition by SQL

I have a SQL Server database with a table that gets data from Time and Attendance devices.

It looks like this:

ID UserID Date Time SignDirection
1988781 25000 2022/01/11 10:02 1
1988782 25000 2022/01/11 10:03 1
1988783 25000 2022/01/11 10:04 1
1988784 25000 2022/01/11 12:30 2
1988785 25000 2022/01/11 12:31 2
1988786 25001 2022/01/11 10:00 1
1988787 25001 2022/01/11 12:30 2
1988788 25002 2022/01/11 10:15 1
1988789 25002 2022/01/11 10:16 1
1988790 25002 2022/01/11 12:19 2

How to filter the data for each User ID to have lower time for SignDirection = 1, and the higher time for SignDirection = 2 ?

To be like :

ID UserID Date Time SignDirection
1988781 25000 2022/01/11 10:02 1
1988785 25000 2022/01/11 12:31 2
1988786 25001 2022/01/11 10:00 1
1988787 25001 2022/01/11 12:30 2
1988788 25002 2022/01/11 10:15 1
1988790 25002 2022/01/11 12:19 2

I have tried

select distinct

but had no success with that.

Upvotes: 0

Views: 144

Answers (2)

Charlieface
Charlieface

Reputation: 71578

The answer given by @LukStorms is good, but it requires another sort for the second row-number.

You can instead use LEAD with the same sort to detect the last row

SELECT
  ID,
  UserID,
  [Date],
  [Time],
  SignDirection
FROM
(
    SELECT *
    , ROW_NUMBER() OVER (PARTITION BY UserID, SignDirection, [Date] ORDER BY [Time]) rn_asc
    , LEAD(Time) OVER (PARTITION BY UserID, SignDirection, [Date] ORDER BY [Time]) nextTime
    FROM YourTable t
) q
WHERE ((SignDirection = 1 AND rn_asc = 1) OR
       (SignDirection = 2 AND nextTime IS NULL)) 
ORDER BY
  ID,
  UserID,
  [Date],
  [Time],
  SignDirection;

db<>fiddle

Upvotes: 1

LukStorms
LukStorms

Reputation: 29657

You can calculate 2 row_number, up & down.
Then filter on them.

SELECT ID, UserID, [Date], [Time], SignDirection
FROM
(
    SELECT *
    , ROW_NUMBER() OVER (PARTITION BY UserID, SignDirection, [Date] ORDER BY [Time] ASC) rn_asc
    , ROW_NUMBER() OVER (PARTITION BY UserID, SignDirection, [Date] ORDER BY [Time] DESC) rn_desc
    FROM your_time_and_attendance_table t
) q
WHERE ((SignDirection = 1 AND rn_asc = 1) OR
       (SignDirection = 2 AND rn_desc = 1)) 
ORDER BY ID, UserID, [Date], [Time], SignDirection

Upvotes: 1

Related Questions