First entry is break out second entry is break in

Transact-SQL
1

...
declare @events table (empcod varchar(6), trans_date date, time_portion varchar(8))
insert @events select '210398', '2023-01-21', '12:20:05'
insert @events select '210398', '2023-01-21', '12:20:07'
insert @events select '210398', '2023-01-21', '12:43:00'
insert @events select '210398', '2023-01-21', '12:43:02'
insert @events select '210398', '2023-01-21', '12:56:01'
insert @events select '210398', '2023-01-21', '12:59:03'
insert @events select '215003', '2023-01-21', '12:37:00'
insert @events select '215003', '2023-01-21', '12:37:02'
insert @events select '220109', '2023-01-21', '12:04:02'
insert @events select '220109', '2023-01-21', '12:04:04'

;with cte as
(
select empcod, trans_date , time_portion, rn= row_number() over(partition by empcod order by empcod, trans_date, time_portion)
from @events t
where trans_date='2023-01-21'
and time_portion >'12:00:00'
)
, CTE2
as
(
select empcod, trans_date, time_portion, INOUT= case when rn%2 =1 then 'IN' else 'OUT' END
from cte
)
select empcod
, trans_date
, breakout = case when inout='in' then time_portion end
, breakin = case when inout='out' then time_portion end
from cte2
...

The result is

empcod trans_date breakout breakin
210398 2023-01-21 12:20:05 NULL
210398 2023-01-21 NULL 12:20:07
210398 2023-01-21 12:43:00 NULL
210398 2023-01-21 NULL 12:43:02
210398 2023-01-21 12:56:01 NULL
210398 2023-01-21 NULL 12:59:03
215003 2023-01-21 12:37:00 NULL
215003 2023-01-21 NULL 12:37:02
220109 2023-01-21 12:04:02 NULL
220109 2023-01-21 NULL 12:04:04

I want the result to be

empcod trans_date breakout breakin
210398 2023-01-21 12:20:05 12:20:07
210398 2023-01-21 12:43:00 12:43:02
210398 2023-01-21 12:56:01 12:59:03
215003 2023-01-21 12:37:00 12:37:02
220109 2023-01-21 12:04:02 12:04:04

2

hi

hope this helps

; with cte_rn as 
  ( select rank() over( order by empcod, time_portion) as rn,  * from @events ) 
select 
     floor( (rn - 1) / 2)
  ,  empcod
  ,  min(time_portion)as breakout
  ,  max(time_portion)  as breakin
from 
   cte_rn 
group by 
       empcod
	 , floor( (rn - 1) / 2)

image

3

@harishgg1

Just like that!!!

I was sure that I am doing long way and there has to be short and simple query.

Love U.

4

@harishgg1
There is a catch
If we remove the last line of sample data ( A person went for lunch break and did not come back)
, then in the result line no 5 breakin column should be NULL. Currently it is filling with breakout column.

5

@harishgg1

I replaced
, min(time_portion)as breakout
, max(time_portion) as breakin
with
, max(case when rn%2=1 then time_portion end) as breakout
, max(case when rn%2=0 then time_portion end) as breakin
and it seems to be ok

6

Just a word of caution...
The title of this post is "First entry is break out second entry is break in". This could lead to some serious issues because there is absolutely nothing in the data that guarantees that.