Get IN and OUT date from booking table

For example I have this kind of table

----------------------------------------------------------------------
|      ID      |     USER    |   READER    |         DATETIME        |
----------------------------------------------------------------------
|       1      |      1      |   READER1   | 2020-01-14 08:12:43.000 |
|       1      |      1      |   READER2   | 2020-01-14 08:13:12.000 |
|       1      |      1      |   READER2   | 2020-01-14 10:12:19.000 |
|       1      |      1      |   READER1   | 2020-01-14 10:13:23.000 |
|       1      |      1      |   READER1   | 2020-01-14 10:30:43.000 |
|       1      |      1      |   READER1   | 2020-01-14 11:30:43.000 |
----------------------------------------------------------------------

I want to get the IN and OUT date of a user per reader

Example :

-------------------------------------------------------------------------------------
|    USER    |    READER    |             IN            |             OUT           |
-------------------------------------------------------------------------------------
|      1     |    READER1   |  2020-01-14 08:12:43.000  |  2020-01-14 10:13:23.000  |
|      1     |    READER2   |  2020-01-14 08:13:12.000  |  2020-01-14 10:12:19.000  |
|      1     |    READER1   |  2020-01-14 10:30:43.000  |  2020-01-14 11:30:43.000  |
-------------------------------------------------------------------------------------

How could I get this result?

Answer

I understand that you want to group every two records of the same user/reader. You can use row_number() and lead() for this:

select usr, reader, datetime [in], lead_datetime [out]
from (
    select 
        t.*,
        row_number() over(partition by usr, reader order by datetime) rn,
        lead(datetime) over(partition by usr, reader order by datetime) lead_datetime
    from mytable t
) t
where rn % 2 = 1
order by [in]

Demo on DB Fiddle:

usr | reader  | in                  | out                
--: | :------ | :------------------ | :------------------
  1 | READER1 | 2020-01-14 08:12:43 | 2020-01-14 10:13:23
  1 | READER2 | 2020-01-14 08:13:12 | 2020-01-14 10:12:19
  1 | READER1 | 2020-01-14 10:30:43 | 2020-01-14 11:30:43