I have a table of data that has the following columns
EmployeeID-Date-Time
The data is going into a timeclock system. I am ultimately trying to find a way to sequence the data by employee because my output needs to look like this.
EmployeeID-DateIN-TimeIN-DateOut-Timeout
The problem is that there is no easy way to tell how many entries per employee there could be in a day. These are bus drivers so they clock in and out for their morning route, afternoon route, and possibly even fieldtrips in the middle. Plus what happens if they miss a punch and now have an uneven number of rows.
Anyway I am trying to get the following example as my input and output.
Input 12345-8/25/22-08:45 12345-8/25/22-09:45 12345-8/25/22-13:00 12345-8/25/22-13:30 23456-8/25/22-10:00 23456-8/25/22-11:00 Output 12345-8/25/22-08:45-8/25/22-09:45 12345-8/25/22-13:00-8/25/22-13:30 23456-8/25/22-10:00-8/25/22-11:00
I am very new to SQL so I don’t even know if it’s possible but appreciate the help!
Advertisement
Answer
select id
,date
,date2
from (
select id
,dt as "date"
,lead(dt) over (partition by id order by dt) as "date2"
,row_number() over (partition by id order by dt) as rn
from t
) t
where rn % 2 != 0
order by id
| id | date | date2 |
|---|---|---|
| 12345 | 2022-08-25 08:45:00.000 | 2022-08-25 09:45:00.000 |
| 12345 | 2022-08-25 13:00:00.000 | 2022-08-25 13:30:00.000 |
| 23456 | 2022-08-25 10:00:00.000 | 2022-08-25 11:00:00.000 |