Query to concatenate rows based on data from multiple rows

I’m trying to print out xml results with a query in SQL Server 2014.

The query is supposed return data from a date range, and return a concatenated list of player names and player times.

The concatenation would ONLY occur if the playEnd was within 30 minutes of the next players playStart.

So if I have data like this:

Name        PlayDate         PlayStart      PlayEnd
----------------------------------------------------
player1 |   10/8/2018    |   08:00:00   |   09:00:00
player2 |   10/8/2018    |   09:10:00   |   10:10:00
player3 |   10/9/2018    |   10:40:00   |   11:30:00
player4 |   10/11/2018   |   08:30:00   |   08:37:00
player5 |   10/11/2018   |   08:40:00   |   08:50:00  
player6 |   10/12/2018   |   09:00:00   |   09:45:00
player7 |   10/12/2018   |   09:50:00   |   10:10:00
player8 |   10/12/2018   |   10:30:00   |   12:20:00

player1 and player2 play times would be concatenated together like: player1, player2 = 8:00:00 – 10:10:00 for 10/8/2018
player3 would just be: player3 = 10:40:00 – 11:30:00 for 10/9/2018
player4 and player5 play times would be concatenated like: player4, player5 = 08:30:00 – 08:50:00 for 10/11/2018
player6 and player7 and player8 play times would be concatenated like: player6, player7, player8 = 09:00:00 – 12:20:00 for 10/12/2018

I’ve tried modifying the query below in many ways, but I just don’t know how to compare one row of data with the next and then combine the two (or more) if needed.

select Name, PlayDate, PlayStart, PlayEnd
where playDate between '10/8/2018' and '10/12/2018'
for xml auto

Is there a way of doing this with SQL Server 2014?

Thanks!

Answer

Assuming games are not played around midnight, you can do this using lag() and cumulative sums:

select t.*,
       sum(case when prev_playEnd is null or prev_playEnd < dateadd(-30, minute, playEnd)
                then 1 else 0
           end) over (partition by playdate order by playStart
                     ) as grouping
from (select t.*,
             lag(playEnd) over (partition by playdate order by playStart) as prev_playEnd
      from t
     ) t;

The column grouping has the information you want.

Advertisement

Answer