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 |