I have a table with the below structure and data.
| sqlid |
logdate |
empcode |
timein1 |
timeout1 |
timein2 |
timeout2 |
timein3 |
timeout3 |
timein4 |
timeout4 |
timein5 |
timeout5 |
| 2 |
2022-08-24 |
5 |
10:15 |
14:03 |
14:18 |
19:41 |
|
|
|
|
|
|
| 3 |
2022-08-24 |
6 |
11:02 |
13:57 |
14:41 |
15:48 |
15:55 |
16:19 |
16:33 |
19:45 |
|
|
| 4 |
2022-08-24 |
7 |
09:35 |
11:13 |
11:31 |
16:47 |
17:13 |
18:08 |
18:55 |
19:23 |
|
|
| 5 |
2022-08-24 |
9 |
10:03 |
12:45 |
12:56 |
14:05 |
14:41 |
18:11 |
18:22 |
20:04 |
|
|
| 6 |
2022-08-24 |
10 |
09:51 |
10:31 |
11:48 |
13:52 |
14:28 |
16:41 |
17:21 |
18:25 |
|
|
| 7 |
2022-08-24 |
12 |
09:58 |
13:52 |
14:28 |
16:24 |
16:40 |
17:20 |
18:13 |
18:36 |
|
|
| 8 |
2022-08-24 |
16 |
09:24 |
13:17 |
13:56 |
19:01 |
|
|
|
|
|
|
| 9 |
2022-08-24 |
18 |
09:41 |
10:57 |
12:42 |
13:53 |
13:59 |
17:13 |
17:40 |
17:58 |
|
|
| 11 |
2022-08-24 |
29 |
09:19 |
17:37 |
22:53 |
|
|
|
|
|
|
|
| 12 |
2022-08-24 |
31 |
09:57 |
13:17 |
14:02 |
14:18 |
15:55 |
16:10 |
16:31 |
18:41 |
|
|
I want to convert the above data as below table structure.
| empcode |
StartTime |
EndTime |
| 5 |
10:15 |
14:03 |
| 5 |
14:18 |
19:41 |
| 6 |
11:02 |
13:57 |
| 6 |
14:41 |
15:48 |
| 6 |
15:55 |
16:19 |
| 6 |
16:33 |
19:45 |
| 7 |
09:35 |
11:13 |
| 7 |
11:31 |
16:47 |
| 7 |
17:13 |
18:08 |
| 7 |
18:55 |
19:23 |
Please help me to write a SQL Server query to get the output.