clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1305712 fiddles created (20082 in the last week).

CREATE TABLE dbo.Matches(DateField DATE,HomeTeam varchar(100),AwayTeam varchar(100));
 hidden batch(es)


INSERT INTO dbo.Matches VALUES ('2018-08-10', 'Man United', 'Leicester'), ('2018-08-11', 'Bournemouth', 'Cardiff'), ('2018-08-11', 'Fulham', 'Crystal Palace'), ('2018-08-11', 'Huddersfield', 'Chelsea'), ('2018-08-11', 'Newcastle', 'Tottenham'), ('2018-08-11', 'Watford', 'Brighton'), ('2018-08-11', 'Wolves', 'Everton'), ('2018-08-12', 'Arsenal', 'Man City'), ('2018-08-12', 'Liverpool', 'West Ham'), ('2018-08-12', 'Southampton', 'Burnley'), ('2018-08-18', 'Cardiff', 'Newcastle'), ('2018-08-18', 'Chelsea', 'Arsenal'), ('2018-08-18', 'Everton', 'Southampton'), ('2018-08-25', 'Round3Example_1', 'Round3Example_2')
14 rows affected
 hidden batch(es)


;WITH CTE AS ( SELECT DateField, CASE WHEN DATEDIFF(day,LAG(DateField) OVER(ORDER BY DateField),DateField) > 3 THEN 1 ELSE 0 END as newround, Hometeam, AwayTeam FROM dbo.Matches ) SELECT Datefield, HomeTeam,AwayTeam, SUM(newround) OVER(ORDER BY DateField) + 1 FROM CTE;
Datefield HomeTeam AwayTeam (No column name)
10/08/2018 00:00:00 Man United Leicester 1
11/08/2018 00:00:00 Bournemouth Cardiff 1
11/08/2018 00:00:00 Fulham Crystal Palace 1
11/08/2018 00:00:00 Huddersfield Chelsea 1
11/08/2018 00:00:00 Newcastle Tottenham 1
11/08/2018 00:00:00 Watford Brighton 1
11/08/2018 00:00:00 Wolves Everton 1
12/08/2018 00:00:00 Arsenal Man City 1
12/08/2018 00:00:00 Liverpool West Ham 1
12/08/2018 00:00:00 Southampton Burnley 1
18/08/2018 00:00:00 Cardiff Newcastle 2
18/08/2018 00:00:00 Chelsea Arsenal 2
18/08/2018 00:00:00 Everton Southampton 2
25/08/2018 00:00:00 Round3Example_1 Round3Example_2 3
 hidden batch(es)