I've got a table with 'year' and 'month' columns used to track quantities (of widgets sold, doughnuts eaten - doesn't really matter) for certain objects in a given month. The reason that only monthly totals are kept instead of creating a record for every transaction is that the table would otherwise have 100's of millions of rows vs. a couple hundred thousand.
I need to do ad-hoc queries for date ranges, say October 2002 through March 2003. I've taken to creating a 'fake' date field in the database constructed from the month & year columns to make querying easier. For instance: vmonth vyear vdate 1 2000 01-01-2000 9 2001 09-01-2001 etc... If someone asks for Oct 2002 to Mar 2003, I create a couple of dates to be used in a query. <cfset bdate = "#begmonth#/1/#begyear#"> <cfset edate = "#endmonth#/1/#endyear#"> <cfquery datasource="#dsn#" name="r"> SELECT vdate, SUM(items) AS itemtotal FROM stats WHERE itemcode = '#form.itemcode#' AND vdate BETWEEN #bdate# AND #edate# GROUP BY vdate </cfquery> which seems to make querying the data easier. Is there any way that I could eliminate the redundant 'date' field and do queries only using the month & year columns? Thanks. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
