Combine multiple rows of data into one (Start and End time)-CodePudding

I'm currently receiving notifications of when a device is switched on, and another when the device is switched off. These are currently showing in separate rows, however I'd like to combine the one/off record of each instance into one row

The data is entering as below:

ObjectID     On/OffID     Msgtime
100          1            2022-04-15 10:01:00
1472         1            2022-04-15 10:04:00
100          0            2022-04-15 11:35:00
100          1            2022-04-15 12:00:00
1472         0            2022-04-15 15:00:00

I'd like to have it showing as below:

ObjectID     OnTime               OffTime
100          2022-04-15 10:01:00  2022-04-15 11:35:00
1472         2022-04-15 10:04:00  2022-04-15 15:00:00
100          2022-04-15 12:00:00  -

CodePudding user response：

Maybe a group by query like below on row_number column

see fiddle link

select objectID, 
       min(msgTime) as OnTime, 
       case 
         when min(MsgTime) <>max(MsgTime) 
         then max(MsgTime) else NULL 
end as OffTime 
from 
(
   select *, 
  row_number() over (partition by ObjectID order by MsgTime asc) 1 as r 
  from T
 )T
group by objectID, r/2
order by Objectid, r/2

CodePudding user response：

This query would return all 'going to on state' rows, and for each one it finds the nearest 'going to off state' row, if exists (LEFT JOIN)

select 
   ontm.ObjectID, ontm.MsgTime as OnTime, offtm.MsgTime as OffTime
from yourtable ontm
     left join
     yourtable offtm
     on ontm.ObjectId=offtm.ObjectId
        and offtm.onoffid = 0
        and ontm.MsgTime <= offtm.MsgTime
        and not exists (select 1
                        from yourtable mdle
                        where mdle.ObjectId=offtm.ObjectId
                          and mdle.MsgTime < offtm.MsgTime
                          and ontm.MsgTime < mdle.MsgTime  
                       )  
where ontm.onoffid = 1