Welcome to the Hawk Haven

Sorry, no data table was returned from this Crunch.

SET @temp = '', @versus = '', @versus2 = '', @game = 0;

WITH inside AS
(SELECT _when, _rnd, _ha, _tm, _op, _vn, _score, _cwd, _tmRung, _opRung, _tmFin, _opFin, _for AS '_for', _agt AS '_agt', (_for) - (_agt) AS '_mgn', IF((_for) - (_agt) > 0, 'W', IF((_for) - (_agt) = 0, 'D', 'L')) AS '_WL', ROW_NUMBER() OVER (PARTITION BY _tm ORDER BY _when DESC) as '_game', DATEDIFF(_when, LAG(_when, 1) OVER (PARTITION BY _tm ORDER BY _when)) AS '_tmDays', DATEDIFF(_when, LAG(_when, 1) OVER (PARTITION BY _op ORDER BY _when)) AS '_opDays', LAG(_vn, 1) OVER (PARTITION BY _tm ORDER BY _when) AS '_pre' FROM haven WHERE YEAR(_when) BETWEEN 2010 AND 2023),

middle AS (SELECT *,

@temp := if (_tm > _op, CONCAT(LEFT(_op, 2), LEFT(_tm, 2)), CONCAT(LEFT(_tm, 2), LEFT(_op, 2))) AS _temp,
@versus2 := IF (_HA = 'H' AND LOCATE(@temp, @versus) > 0, CONCAT(@versus2, @temp, '.'), @versus2) AS _V2,
@versus := IF (_HA = 'H' AND LOCATE(@temp, @versus) = 0, CONCAT(@versus, @temp, '.'), @versus) AS _V1,
@game := @game + 1,

ROW_NUMBER() OVER (ORDER BY _when DESC) as '_rank' FROM inside WHERE YEAR(_when) = 2023 AND _rnd < 30)

SELECT @versus, @game, CHAR_LENGTH(@versus2), CHAR_LENGTH(@versus), _tm AS 'Team', count(*) AS 'P', SUM(IF(_for > _agt, 1, 0)) AS 'W', SUM(IF(_for = _agt, 1, 0)) AS 'L', SUM(IF(_for = _agt, 1, 0)) AS 'D', SUM(_for) as 'For_.0', SUM(_agt) as 'Agt', SUM(IF(_for > _agt, 4 / IF(LOCATE(_temp, @versus2) > 0, 2, 1), IF(_for = _agt, 2 / IF(LOCATE(_temp, @versus2) > 0, 2, 1), 0))) AS 'Pts', 100 * SUM(_for) / SUM(_agt) AS '%_.1%' FROM middle WHERE 1 GROUP BY _tm ORDER BY SUM(IF(_for > _agt, 40, IF(_for = _agt, 20, 0))) + (SUM(_for) / SUM(_agt)) DESC

Very much a work in progress.

Imagine a perfect season - where each team plays each other twice, home and away.

This crunch goes back to find the last time each team played each other. Even back to 2017 for the last time the Eagles played the Lions at home.

You can find the ladder in the new 'Crunch' section (menu to the left).

So how does everyone else hold up?

Well, it seems the Hawks are doing quite well!

The tables are fully scripted! You can sort the columns by clicking on the column header (click a second time to reverse the sort). Hovering over the column name should give you a descriptor and/or and average value for that column. And clicking the team name should open up a second table with more detailed breakdown of the last 25 games involved.