2

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
  • Can matches be postponed? What about draws/replays? – Vérace Oct 10 at 9:51
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

  • worked like a charm, thanks a lot! Results correspond to rounds in reality as well! Cheers – Dan Oct 14 at 10:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.