I am able to return the number of games completed each week
using this SQL:
select g.gamenum,
DATEFORMAT(g.gamedate, '%W, %M %e') as gamedate,
g.homeid,
h.teamname home,
g.homescore hscore,
g.visitorid,
v.teamname visitor,
g.visitorscore vscore
from games g,
teams v, teams h
where (g.homeid = h.id and g.visitorid = v.id)
and (WEEK(g.gamedate) = WEEK(CURDATE()))
and ( g.homescore is not null and g.visitorscore is not null )
order by gamedate
Now I need to also return the win. loss, and tie records for each
team up to the CURDATE.
Here is how I do it normally. I pass each record returned above
to a function that builds the records.
/*
999 as score means rainout, 777 as score means game
cancelled or moved
*/
$rsql = "SELECT homescore, visitorscore, homeid, visitorid
FROM games
WHERE ( homeid={$item['homeid']} OR visitorid={$item['homeid']} )
AND homescore <> 999 AND visitorscore <> 999
AND homescore <> 777 AND visitorscore <> 777
AND ( homescore <> ' ' OR visitorscore <> ' ' )
AND gamedate <= '{$item['gamedate']}'
order by gamedate";
Then I walk the result and increment wins, losses or ties based
upon the result.
Could I do both of these operations with just one SQL statement?
Here's the schema for the games table:
[pre]
gameid int,
gamenum int,
gamedate datetime,
visitorid int,
homeid int,
visitorscore int,
homescore int,
fieldid int,
modified smallint
[/pre]
