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