SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT *
INTO LogPlayer
FROM (
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180100 AS [TIMEID]
    UNION ALL
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180200 AS [TIMEID]
    UNION ALL
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180300 AS [TIMEID]
    UNION ALL
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180600 AS [TIMEID]
    UNION ALL
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180700 AS [TIMEID]
    UNION ALL
    SELECT 'Luke' AS [Player], 'Yellow' AS [Team], 20180100 AS [TIMEID]
    UNION ALL
    SELECT 'Luke' AS [Player], 'Yellow' AS [Team], 20190100 AS [TIMEID]
) B

7 rows affected

select *
from logplayer


Player
Team
TIMEID


John
Red
20180100

John
Red
20180200

John
Red
20180300

John
Red
20180600

John
Red
20180700

Luke
Yellow
20180100

Luke
Yellow
20190100


select player, team, min(timeid), max(timeid)
from (select lp.*,
             row_number() over (partition by player, team order by timeid) as seqnum,
             cast(cast(timeid + 1 as varchar(255)) as datetime) as yyyymm
      from logplayer lp
     ) lp
group by player, team, dateadd(month, - seqnum, yyyymm)
order by player, team, min(timeid)


player
team
(No column name)
(No column name)


John
Red
20180100
20180300

John
Red
20180600
20180700

Luke
Yellow
20180100
20180100

Luke
Yellow
20190100
20190100

Player	Team	TIMEID
John	Red	20180100
John	Red	20180200
John	Red	20180300
John	Red	20180600
John	Red	20180700
Luke	Yellow	20180100
Luke	Yellow	20190100