SQL - merge rows into one?

Question

I am trying to build a report from an old, badly structured telephony system. The table structure I have is below

 User         Interval                          Logon                            Logoff
 3356        2020-03-27 08:45:00.000            2020-03-27 08:51:45.0620000      NULL
 3356        2020-03-27 09:00:00.000            NULL                             NULL
 3356        2020-03-27 09:15:00.000            NULL                             NULL
 3356        2020-03-27 09:30:00.000            NULL                             2020-03-27 09:32:23.000   
 3356        2020-03-27 09:45:00.000            NULL                             NULL
 3356        2020-03-27 10:00:00.000            2020-03-27 10:11:25.0620000      NULL
 3356        2020-03-27 10:15:00.000            NULL                             2020-03-27 10:11:18.0620000

The data is grouped by 15 minute chunks and shows any log ons or log offs that have occurred during those 15 minutes. What I want to do is produce an output that puts any logons on the same row as their next log offs. My output would look like the below.

 User         Interval                          Logon                            Logoff
 3356        2020-03-27                        2020-03-27 08:51:45.0620000       2020-03-27 09:32:23.000   
 3356        2020-03-27                        2020-03-27 10:11:25.0620000       2020-03-27 10:11:18.0620000

I tried the solution here (My Sql merging rows) but this was just giving me the first log on and last log off for the entire day - missing things in between.

score 1 · Answer 1 · answered Mar 30 '20 at 10:35

You can use lead() and filtering. Assuming the values are correctly interleaved:

select user, convert(date, interval), logon
       coalesce(logoff, next_logoff) as logoff
from (select t.*, 
             lead(logoff) over (partition by user order by interval) as next_logoff
      from t
      where logon is not null or logoff is not null
     ) t
where logon is not null;

This should also handle the situation when the logoff and logon are in the same interval.

SQL - merge rows into one?

1 Answers1