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'
BUT I NEED TO SHOW THE RESULT LIKE THIS
Advertisement
Answer
Assuming that your in
and out
s 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