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!

CodePudding user response：

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

Fiddle

CodePudding user response：

Just to offer this as an another simple way to pivot in groups of two, which will give your desired results provided your rows are in pairs. To handle any edge cases you really need better data though.

select id,
  Max(case when seq % 2 = 1 then [date] end) dateIn,
  Max(case when seq % 2 = 0 then [date] end) dateOut
from (
  select *, Row_Number() over(partition by id order by [date]) seq
  from t
)t
group by id, seq   (seq % 2)
order by id, dateIn;

DB<>Fiddle

CodePudding user response：

You can try this one

 WITH T1 AS (
 SELECT EmpId, InOut, ROW_NUMBER() OVER(PARTITION BY EmpId ORDER BY InOut) AS RowNumber
 FROM Clock )

 SELECT T1.EmpId, T1.InOut AS I, T2.InOut AS O
 FROM T1 
    INNER JOIN T1 AS T2 ON T1.EmpId = T2.EmpId AND  T1.RowNumber   1 = T2.RowNumber 
                                               AND  T2.RowNumber % 2 = 0