SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
Query Library
| Casey Trauer submitted this query on: February 03, 2010 7:00pm EST | |
| TITLE | Find the most recent documents for fixture key and team |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT dn.full_name, t.team_key, docs.doc_id, CONVERT_TZ(docs.date_time,'+00:00','-05:00'), docs.db_loading_date_time FROM documents docs JOIN document_fixtures df ON docs.document_fixture_id = df.id JOIN teams_documents td ON td.document_id = docs.id JOIN teams t ON td.team_id = t.id JOIN display_names dn ON (dn.entity_id = t.id AND dn.entity_type = 'teams') JOIN publishers pub ON pub.id = docs.publisher_id WHERE df.fixture_key = 'roster' AND t.team_key LIKE 'l.nhl.com-t.%' AND TIMESTAMPDIFF(HOUR,CONVERT_TZ(NOW(),'-05:00','+00:00'),docs.date_time) > '-24' AND pub.publisher_key = 'sportsnetwork.com' ORDER BY docs.date_time DESC; |
| DESCRIPTION | This query tracks all the roster documents published in the past 24 hours for each team in the NHL. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |