CODEWITHSUNDEEP
sql
user18245679
user18245679

Reputation: 31

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

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  -

Upvotes: 2

Views: 168

Answers (2)

tinazmu
tinazmu

Reputation: 5139

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

Explanation: We first select all 'going to on' rows; these are the ones we want a result row. We then find all 'matching', i.e. future, 'going to off' state records for the same objectid (we use LEFT JOIN to make sure that if the objectId was left ON we still show it). This would match all future 'going to off' rows for the object, so we need something to make sure that only the earliest one matches our ON row; we do this by making sure that for any candidate OFF row, there is no other future, earlier OFF row: NOT EXISTS.

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

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

Upvotes: 1

Related Questions