Add Gameweek Fixeture based on Datefield

Question

having the following table i need to add a new Column called "Round" to represent Round 1, Round 2 and so on, based on the Date Column in the table. Same Round should be given to matches happening in a 3 days timespan (some datediff to be used, can;t figure it out)

Initial Table

Date            HomeTeam     AwayTeam
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

Desired Output

Date            HomeTeam     AwayTeam          RoundNumber
2018-08-10     Man United    Leicester         1
2018-08-11     Bournemouth   Cardiff           1
2018-08-11      Fulham       Crystal Palace    1
2018-08-11    Huddersfield   Chelsea           1
2018-08-11      Newcastle    Tottenham         1
2018-08-11       Watford     Brighton          1
2018-08-11       Wolves      Everton           1
2018-08-12      Arsenal      Man City          1
2018-08-12     Liverpool     West Ham          1
2018-08-12    Southampton    Burnley           1
2018-08-18    Cardiff        Newcastle         2
2018-08-18     Chelsea       Arsenal           2
2018-08-18     Everton       Southampton       2

Answer 1

If the gap between matches is always more than 3 days, you could use this:

;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 as [Round]
FROM CTE;

DDL & DML

CREATE TABLE dbo.Matches(DateField DATE,HomeTeam varchar(100),AwayTeam varchar(100));
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');

Result

Datefield   HomeTeam              AwayTeam            Round
2018-08-10  Man United            Leicester           1
2018-08-11  Bournemouth           Cardiff             1
2018-08-11  Fulham                Crystal Palace      1
2018-08-11  Huddersfield          Chelsea             1
2018-08-11  Newcastle             Tottenham           1
2018-08-11  Watford               Brighton            1
2018-08-11  Wolves                Everton             1
2018-08-12  Arsenal               Man City            1
2018-08-12  Liverpool             West Ham            1
2018-08-12  Southampton           Burnley             1
2018-08-18  Cardiff               Newcastle           2
2018-08-18  Chelsea               Arsenal             2
2018-08-18  Everton               Southampton         2
2018-08-25  Round3Example_1       Round3Example_2     3

Db<>fiddle