Skip to content
Advertisement

SQL QUERY TO SHOW FIRST IN AND FIRST OUT

my query like this :

 select u.name, (case when IOType=0 then format(Edatetime,'hh:mm tt') end) as 'IN' , 
       (case when IOType=1 then format(Edatetime,'hh:mm tt') end) as 'out'
        from TestZEOTRA T 
        inner join Mx_UserMst U on t.UsrRefCode=u.UserID
        where UsrRefCode='1506' and CAST(Edatetime as date)='28 OCT 2019'

output getting : enter image description here

BUT I NEED TO SHOW THE RESULT LIKE THIS enter image description here

Advertisement

Answer

Assuming that your in and outs are interleaved, you can use row_number() and aggregation:

select ram, min(in) as in, min(out) as out
from (select t.*, 
             row_number() over (partition by name, out order by in) as seqnum_in,
             row_number() over (partition by name, in order by in) as seqnum_out
      from t
     ) t
group by (case when in is null then seqnum_out else seqnum_in);

For your particular query, this is a little simpler:

select u.name,
       min(case when IOType = 0 then format(t.Edatetime, 'hh:mm tt') end) as in_time
       max(case when IOType = 1 then format(t.Edatetime, 'hh:mm tt') end) as out_time
from  Mx_UserMst U join
      (select t.*,
              row_number() over (partition by t.userrefcode, t.iotype order by t.edatetime) as seqnum
       from TestZEOTRA T    
       where UsrRefCode = '1506' and
             cast(t.Edatetime as date) = '2019-10-28'
      ) t
      on t.UsrRefCode = u.UserID
group by u.UserID, u.name, seqnum
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement