How to get all punches of employee in a row?

Question

Below is my table structure. I tried to get all punches of employees in the day in a row. Before I get through flag but now the flag is not passed.

My Table:

 Id             DT
7001    2020-08-03 06:22:02.000
7001    2020-08-03 12:28:41.000
7001    2020-08-03 16:20:29.000
7001    2020-08-03 22:32:38.000
7001    2020-08-04 06:24:09.000
7001    2020-08-04 06:24:38.000
7001    2020-08-04 12:18:34.000
7001    2020-08-04 16:27:14.000
7001    2020-08-04 22:27:17.000

Now my expected output exact same as below.

Expected Output:

Id      Date           In               Break         Resume      Out          TotalHrs
7001   2020-08-03   06:22:02.000   12:28:41.000  16:20:29.000  22:32:38.000     10:30
7001   2020-08-04   06:24:09.000   12:18:34.000  16:27:14.000  22:27:17.000     10:30

I tried with below query

select
t.emp_reader_id as empId,cast(max(DT) as date) as Belongs_to, max(iif(EVENTID = 0, DT, null)) 
as EntryTime, max(iif(EVENTID = 1, DT, null)) as ExitTime,
max(iif(EVENTID = 0, DT, null)) as Breakin, max(iif(EVENTID = 1, DT, null)) as Breakout,
format(dateadd(ss,Datediff(second,max(iif(EVENTID = 0, DT, null)),
max(iif(EVENTID = 1, DT, null))),0),'HH:mm') as Worked
from (  
select
*, grp =sum(iif(EVENTID = 1,0, 0) ) over (partition by emp_reader_id order by DT) ,
        grp1 =   sum(iif(EVENTID = 3,4, 0) )over (partition by emp_reader_id order by DT)
    from
        Trnevents
) t inner join employee B on t.emp_reader_id=B.emp_reader_id
where b.emp_reader_id=7001
group by t.emp_reader_id,t.grp,t.Belongs_to

EventId=0 as In EventId=1 as break EventId=3 as resume EventId=4 as out

Now the flag always as

EventId=0

for all employees.

Help appreciated

user_1144035 · Answer

You can pivot the timestamps -- which seems to be the gist of your question -- using conditional aggregation:

select id, convert(date, dt),
       max(case when seqnum = 1 then dt end) as event_1,
       max(case when seqnum = 2 then dt end) as event_2,
       max(case when seqnum = 3 then dt end) as event_3,
       max(case when seqnum = 4 then dt end) as event_4
from (select e.*,
             row_number() over (partition by id, convert(date, dt) order by dt) as seqnum
      from Trnevents e
     ) e
group by id, convert(date, dt);

It seems very dangerous to try to impute what the actual events are:

People may take no breaks.
People may take more than one break.
People may need to leave during the day.
Shifts may go past midnight.
Equipment may "malfunction" and either miss an event or record an event twice.

Without an identification of the type of event, it is just not reasonable to try to figure out anything more from the data you have provided.

How to get all punches of employee in a row?

1 Answers