You could write
AND (
vyear > #begyear#
OR (
Vyear = #begyear#
AND vmonth >= begmonth
)
)
AND (
vyear < #endyear#
OR (
Vyear = #endyear#
AND vmonth <= endmonth
)
)
but it's bit hard on the eyes. I like your existing solution better.
> -----Original Message-----
> From: Jim McAtee [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, 22 July 2003 9:57 a.m.
> To: CF-Talk
> Subject: Month/Year Queries
>
> 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
