Hello this is kindy tricky and I am struggling pretty hard. This goes way over my normal SQL skills :-) Here is the problem I am facing.
I have a gameplan. The basic structure looks like this:
ID League GameDay GameTime HomeTeam AwayTeam
1 1 16.09.2018 10:00 A B
2 1 16.09.2018 10:00 C D
3 1 16.09.2018 14:00 E F
4 1 16.09.2018 14:00 A C
5 1 16.09.2018 17:00 B D
6 1 16.09.2018 17:00 F A
7 1 17.09.2018 10:00 E D
8 1 17.09.2018 10:00 C B
----> This goes on till league 6
I need to query now the minimal break time per Team per Day (and also per league, but the team name is unique). So in the above example Team A´s minimal break time on the 16.09.2018 would be 3 hours (the game from 17:00 minus the game from 14:00).
After a few tries I think I realized I cant do that in one query so I broke it down to one team. And created a few querys with the following result (I selected just the HomeTeam in a query, then the AwayTeam and unioned this two):
League GameDay GameTime Team
1 16.09.2018 10:00 A
1 16.09.2018 14:00 A
1 16.09.2018 17:00 A
1 16.09.2018 10:00 A
1 16.09.2018 10:00 B
1 16.09.2018 17:00 B
1 17.09.2018 10:00 B
---> And so on with the other teams
I have the feeling I am near the desired result but the last step is missing -.-
This is a real pain. First, you need a list of all the games with one column per team:
select id, league, hometeam as team, gameday, gametime
from gameplan
union all
select id, league, awayteam as team, gameday, gametime
from gameplan;
Then, you need to bring in the previous gametime, calculate the datetime difference, and aggregate.
You can use a correlated subquery to get the previous date/time:
select gp.*,
(select max(gp2.dametime)
from gameplan as gp2
where gp.gameday = gp2.gameday and
gp.team in (gp2.hometeam, gp2.awayteam) and
gp2.gametime < gp.gametime
) as prev_gametime
from (select id, league, hometeam as team, gameday, gametime
from gameplan
union all
select id, league, awayteam as team, gameday, gametime
from gameplan
) as gp;
Finally, you want the minimum difference:
select league, team, gamedate,
min(datediff("minute", prev_gametime, gametime)) as minimum_break_length
from (select gp.*,
(select max(gp2.gametime)
from gameplan as gp2
where gp.gameday = gp2.gameday and
gp.team in (gp2.hometeam, gp2.awayteam) and
gp2.gametime < gp.gametime
) as prev_gametime
from (select id, league, hometeam as team, gameday, gametime
from gameplan
union all
select id, league, awayteam as team, gameday, gametime
from gameplan
) as gp
) as gp
group by league, team, gamedate;