I have a database of NFL data. The majority of the data is stored in a
single table, with each row of data containing various info on each game
played. A very simplified version looks like this:
ID TEAM OPPONENT GAME SEASON
SURFACE
5 NY Giants Dallas 10
2000 G
6 Pittsburgh St Louis 11
2000 T
etc.
I am looking to create a query which will display the number of records
where certain conditions are true. Its simple when the criteria for
sorting is all contained on the same row. The twist occurs when I want
to look at games where a TEAM played on a certain SURFACE last game (or
for that matter 5 games ago). This would not be a problem for me if I
made new columns in the database such as SURFACELASTWEEK,
SURFACETWOWEEKSAGO, SURFACETHREEWEEKSAGO, etc., but with over 50 details
about each game that would be a monstrous task and difficult to
maintain. I was thinking maybe I could just add a few new columns that
merely had the reference ID of the last game, 2 games ago, 3 games ago,
etc. But still I'm not sure how to write this query.
This is the abbreviated version of what I have:
<CFQUERY DATASOURCE="nfl" name="SearchResultsQuery">
SELECT SEASON, GAME, DAY
FROM Data
WHERE (SEASON BETWEEN #SeasonLow# AND #SeasonHigh#)
AND (SURFACE = 'G')
AND
<!--- what goes here? ---->
</CFQUERY>
Sorry for the long email, but hopefully what I'm looking for is clear.
Thanks in advance for any help/advice.
Mike
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
