MySQL 8.0

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

create table `match_case_1` as 
SELECT 1 as id, "2019-03-31 00:00:00" as datetime, 1 as status, 29 as gameweek, 12696 as round_id, 1243 as home_team_id UNION ALL 
SELECT 2, "2019-03-31 00:00:00", 1, 29, 12696, 1248 UNION ALL 
SELECT 3, "2019-03-31 00:00:00", 1, 29, 12696, 1242 UNION ALL 
SELECT 4, "2019-03-31 00:00:00", 1, 29, 12696, 1246 UNION ALL 
SELECT 5, "2019-03-31 00:00:00", 1, 29, 12696, 1244 UNION ALL
SELECT 6, "2019-03-31 00:00:00", 1, 29, 12696, 1247 UNION ALL
SELECT 7, "2019-03-31 20:30:00", 1, 29, 12696, 1241 UNION ALL
SELECT 8, "2019-03-31 00:00:00", 1, 29, 12696, 1249 UNION ALL
SELECT 9, "2019-03-31 00:00:00", 1, 29, 12696, 2981 UNION ALL
SELECT 10, "2019-03-31 00:00:00", 1, 29, 12696, 1259
select *
from `match_case_1`


id
datetime
status
gameweek
round_id
home_team_id


1
2019-03-31 00:00:00
1
29
12696
1243

2
2019-03-31 00:00:00
1
29
12696
1248

3
2019-03-31 00:00:00
1
29
12696
1242

4
2019-03-31 00:00:00
1
29
12696
1246

5
2019-03-31 00:00:00
1
29
12696
1244

6
2019-03-31 00:00:00
1
29
12696
1247

7
2019-03-31 20:30:00
1
29
12696
1241

8
2019-03-31 00:00:00
1
29
12696
1249

9
2019-03-31 00:00:00
1
29
12696
2981

10
2019-03-31 00:00:00
1
29
12696
1259


Select m.* from `match_case_1` m where round_id = 12696 and m.datetime = (SELECT COALESCE(MIN(CASE WHEN m2.status < 5 THEN m2.datetime END), MAX(m2.datetime)) FROM `match_case_1` m2 WHERE m2.round_id = m.round_id)


id
datetime
status
gameweek
round_id
home_team_id


1
2019-03-31 00:00:00
1
29
12696
1243

2
2019-03-31 00:00:00
1
29
12696
1248

3
2019-03-31 00:00:00
1
29
12696
1242

4
2019-03-31 00:00:00
1
29
12696
1246

5
2019-03-31 00:00:00
1
29
12696
1244

6
2019-03-31 00:00:00
1
29
12696
1247

8
2019-03-31 00:00:00
1
29
12696
1249

9
2019-03-31 00:00:00
1
29
12696
2981

10
2019-03-31 00:00:00
1
29
12696
1259


create table `match_case_2` as 
SELECT 1 as id, "2019-03-20 00:00:00" as datetime, 5 as status, 29 as gameweek, 12696 as round_id, 1243 as home_team_id UNION ALL 
SELECT 2, "2019-03-20 00:00:00", 5, 29, 12696, 1248 UNION ALL 
SELECT 3, "2019-03-20 00:00:00", 5, 29, 12696, 1242 UNION ALL 
SELECT 4, "2019-03-31 00:00:00", 1, 29, 12696, 1246 UNION ALL 
SELECT 5, "2019-03-31 00:00:00", 1, 29, 12696, 1244 UNION ALL
SELECT 6, "2019-03-31 00:00:00", 1, 29, 12696, 1247 UNION ALL
SELECT 7, "2019-03-31 20:30:00", 1, 29, 12696, 1241 UNION ALL
SELECT 8, "2019-03-31 00:00:00", 1, 29, 12696, 1249 UNION ALL
SELECT 9, "2019-03-31 00:00:00", 1, 29, 12696, 2981 UNION ALL
SELECT 10, "2019-03-31 00:00:00", 1, 29, 12696, 1259
Select m.* from `match_case_2` m where round_id = 12696 and m.datetime = (SELECT COALESCE(MIN(CASE WHEN m2.status < 5 THEN m2.datetime END), MAX(m2.datetime)) FROM `match_case_2` m2 WHERE m2.round_id = m.round_id)


id
datetime
status
gameweek
round_id
home_team_id


4
2019-03-31 00:00:00
1
29
12696
1246

5
2019-03-31 00:00:00
1
29
12696
1244

6
2019-03-31 00:00:00
1
29
12696
1247

8
2019-03-31 00:00:00
1
29
12696
1249

9
2019-03-31 00:00:00
1
29
12696
2981

10
2019-03-31 00:00:00
1
29
12696
1259


create table `match_case_3` as 
SELECT 1 as id, "2019-03-20 00:00:00" as datetime, 5 as status, NULL as gameweek, 12696 as round_id, 1243 as home_team_id UNION ALL 
SELECT 2, "2019-03-20 00:00:00", 5, NULL, 12696, 1248 UNION ALL 
SELECT 3, "2019-03-20 00:00:00", 5, NULL, 12696, 1242 UNION ALL 
SELECT 4, "2019-03-31 00:00:00", 1, NULL, 12696, 1246 UNION ALL 
SELECT 5, "2019-03-31 00:00:00", 1, NULL, 12696, 1244 UNION ALL
SELECT 6, "2019-03-31 00:00:00", 1, NULL, 12696, 1247 UNION ALL
SELECT 7, "2019-03-31 20:30:00", 1, NULL, 12696, 1241 UNION ALL
SELECT 8, "2019-03-31 00:00:00", 1, NULL, 12696, 1249 UNION ALL
SELECT 9, "2019-03-31 00:00:00", 1, NULL, 12696, 2981 UNION ALL
SELECT 10, "2019-03-31 00:00:00", 1, NULL, 12696, 1259
Select m.* from `match_case_3` m where round_id = 12696 and m.datetime = (SELECT COALESCE(MIN(CASE WHEN m2.status < 5 THEN m2.datetime END), MAX(m2.datetime)) FROM `match_case_3` m2 WHERE m2.round_id = m.round_id)


id
datetime
status
gameweek
round_id
home_team_id


4
2019-03-31 00:00:00
1
null
12696
1246

5
2019-03-31 00:00:00
1
null
12696
1244

6
2019-03-31 00:00:00
1
null
12696
1247

8
2019-03-31 00:00:00
1
null
12696
1249

9
2019-03-31 00:00:00
1
null
12696
2981

10
2019-03-31 00:00:00
1
null
12696
1259


create table `match_case_4` as 
SELECT 1 as id, "2019-03-20 00:00:00" as datetime, 5 as status, NULL as gameweek, 12696 as round_id, 1243 as home_team_id UNION ALL 
SELECT 2, "2019-03-20 00:00:00", 5, NULL, 12696, 1248 UNION ALL 
SELECT 3, "2019-03-20 00:00:00", 5, NULL, 12696, 1242 UNION ALL 
SELECT 4, "2019-03-31 00:00:00", 5, NULL, 12696, 1246 UNION ALL 
SELECT 5, "2019-03-31 00:00:00", 5, NULL, 12696, 1244 UNION ALL
SELECT 6, "2019-03-31 00:00:00", 5, NULL, 12696, 1247 UNION ALL
SELECT 7, "2019-03-31 20:30:00", 5, NULL, 12696, 1241 UNION ALL
SELECT 8, "2019-03-31 00:00:00", 5, NULL, 12696, 1249 UNION ALL
SELECT 9, "2019-04-05 00:00:00", 5, NULL, 12696, 2981 UNION ALL
SELECT 10, "2019-04-05 00:00:00", 5, NULL, 12696, 1259
 select m.* from `match_case_4` m 
 WHERE DATE(datetime)>=date(now())
 and DATE(datetime) <= date_add(current_date, INTERVAL 1 week)       
 and m.status =1 and m.round_id = 12696

id	datetime	status	gameweek	round_id	home_team_id
1	2019-03-31 00:00:00	1	29	12696	1243
2	2019-03-31 00:00:00	1	29	12696	1248
3	2019-03-31 00:00:00	1	29	12696	1242
4	2019-03-31 00:00:00	1	29	12696	1246
5	2019-03-31 00:00:00	1	29	12696	1244
6	2019-03-31 00:00:00	1	29	12696	1247
7	2019-03-31 20:30:00	1	29	12696	1241
8	2019-03-31 00:00:00	1	29	12696	1249
9	2019-03-31 00:00:00	1	29	12696	2981
10	2019-03-31 00:00:00	1	29	12696	1259

id	datetime	status	gameweek	round_id	home_team_id
4	2019-03-31 00:00:00	1	null	12696	1246
5	2019-03-31 00:00:00	1	null	12696	1244
6	2019-03-31 00:00:00	1	null	12696	1247
8	2019-03-31 00:00:00	1	null	12696	1249
9	2019-03-31 00:00:00	1	null	12696	2981
10	2019-03-31 00:00:00	1	null	12696	1259