-2

I have table like this

PK_Log_ID   FK_Emp_ID   LogTime Direction
13  3   2022-08-10 09:25:54.563 0
14  3   2022-08-10 13:25:54.563 1
15  3   2022-08-11 09:25:54.563 0
16  3   2022-08-11 11:25:54.563 1
17  3   2022-08-11 12:25:54.563 0
18  3   2022-08-11 13:25:54.563 1
19  3   2022-08-11 14:25:54.563 0
20  3   2022-08-11 18:25:54.563 1
21  4   2022-08-07 09:25:54.563 0
22  4   2022-08-07 13:25:54.563 1
23  4   2022-08-07 14:25:54.563 0
24  4   2022-08-07 18:25:54.563 1
25  4   2022-08-08 09:25:54.563 0
26  4   2022-08-08 13:25:54.563 1

I want like this

PK_Log_ID   FK_Emp_ID   LogTime Direction   Rowmunber
13  3   2022-08-10 09:25:54.563 0   1
14  3   2022-08-10 13:25:54.563 1   2
15  3   2022-08-11 09:25:54.563 0   1
16  3   2022-08-11 11:25:54.563 1   2
17  3   2022-08-11 12:25:54.563 0   3
18  3   2022-08-11 13:25:54.563 1   4
19  3   2022-08-11 14:25:54.563 0   5
20  3   2022-08-11 18:25:54.563 1   6
21  4   2022-08-07 09:25:54.563 0   1
22  4   2022-08-07 13:25:54.563 1   2
23  4   2022-08-07 14:25:54.563 0   3
24  4   2022-08-07 18:25:54.563 1   4
25  4   2022-08-08 09:25:54.563 0   1
26  4   2022-08-08 13:25:54.563 1   2
27  4   2022-08-08 14:25:54.563 0   3
Dale K
  • 25,246
  • 15
  • 42
  • 71
user2626194
  • 9
  • 3

1 Answers1

0

You may use ROW_NUMBER() here, partition by the the employee and date, and order by the timestamp ascending:

SELECT *, ROW_NUMBER() OVER (PARTITION BY FK_Emp_ID, CONVERT(date, LogTime)
                             ORDER BY LogTime) Rowmunber
FROM yourTable
ORDER BY FK_Emp_ID, LogTime;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360