Display attendance data in one row and avoid duplicate value

Question

This is my actual result in SQL Server 2012 with table_name CHECKINOUT

UID CHECKTIME               CHECKTYPE
87  2020-02-19 08:21:33.000 I
87  2020-02-19 11:26:47.000 O
87  2020-02-19 11:27:04.000 I
87  2020-02-19 11:35:25.000 I
87  2020-02-19 11:35:26.000 I
87  2020-02-19 17:21:36.000 O

and I want a result like this. any ideas will highly appreciated.

clockin1                  clockout1                 clockin2                  clockout2
2020-02-19 08:21:33.000   2020-02-19 11:26:47.000   2020-02-19 11:27:04.000   2020-02-19 17:21:36.000

Did the 11:35 times disappear because they are repeated checkin ? — Caius Jard, Mar 24 '20 at 07:06
You need to use `PIVOT`, and more exactly `dynamic PIVOT`. You can read more about this here: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query Regarding the duplicates, you should show the whole query that leads to it. — Alex Brohshtut, Mar 24 '20 at 07:08

score 0 · Answer 1 · answered Mar 24 '20 at 12:16

You can use window functions. I would recommend two separate rows, though:

select uid, checktime as clockin, clockout
from (select cio.*,
             min(case when checktype = 'O' then checktime end) over (partition by uid order by checktime desc) as clockout,
             lag(checktype) over (partition by uid order by checktime desc) as prev_checktype
      from checkinout cio
     ) cio
where (prev_checktype is null or checktype = 'O') and
      checktype = 'I';

I don't recommend trying to put this on a single row, because a user could have more than two checkin/checkout records.

Display attendance data in one row and avoid duplicate value

1 Answers1