Thanks for all the suggestions guys. I actually got the thing working by
using <cfqueryparam> about a minute ago which was a bit of a shock. So now I
have:
<cfset dateFrom = CreateODBCDateTime(CreateDate(form.Year, form.month,
form.day))>
<cfset dateTo = DateAdd('D', 7, dateFrom)>
<cfquery name="NewBranchDetails" dbtype="query">
SELECT Transaction_Date, Sum(Value_Price) as Total_Sales
FROM tempQuery
WHERE Transaction_Date
BETWEEN <cfqueryparam cfsqltype="CF_SQL_DATE" value="#DateFrom#">
AND <cfqueryparam cfsqltype="CF_SQL_DATE" value="#DateTo#">
ORDER BY Transaction_Date asc
</cfquery>
Works a treat....however GROUP didn't seem to do much for it. Anyone know
whether GROUP BY works in QoQ?
Phil
"Spike" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]
>
> You can have different data types in query of queries, but since
> ColdFusion does automatic type conversion it will convert a timestamp to
> a string. You can avoid that if you are creating the columns by
> converting the timestamp to it's numeric equivalent thusly:
>
> <cfset numberdate = somedate + 0>
>
> Once you have done that you should be able to perform BETWEEN
> comparisons providing the dates in the columns are stored as numeric
> values (or as dates if the query is from a database).
>
> You can do group by and aggregate functions as of CFMX.
>
> Lots of information about how to do that is at the following link:
>
> http://makeashorterlink.com/?I17921CA3'
>
> Spike
>
>
> Stephen Milligan
> Consultant for hire
> http://spikefu.blogspot.com
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Steve
> Onnis
> Sent: 04 March 2003 16:10
> To: CFAussie Mailing List
> Subject: [cfaussie] RE: Date problems in Query of Queries
>
>
> Phil
>
> From what I recall, there is no such thing as column types in QofQ.
> Once you start to query a query, all columns are strings and integers.
>
> Also, I dont think you can "group by" with query of query either, and am
> almost certain you cant use aggregated functions like SUM, COUNT and so
> on.
>
> Regards
> Steve Onnis
> Domain Concept Designs
> +61 422 337 685
> +61 3 9431 4249
> http://www.domainconceptdesigns.com
> [EMAIL PROTECTED]://www.cfcentral.com.au
> [EMAIL PROTECTED]
>
>
> ("If you think it can't be done, you haven't asked me!") - Steve Onnis
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Phil
> Rasmussen
> Sent: Tuesday, March 04, 2003 4:01 PM
> To: CFAussie Mailing List
> Subject: [cfaussie] Date problems in Query of Queries
>
>
> Hey all, just wondering if anyone has experienced problems using
> DateStamps in Query of Queries......in WHERE clauses specifically. All
> I am trying to do is a simple WHERE Date BETWEEN #DateFrom# AND #DateTo#
> but I keep getting syntax errors. Both date variables are created using
> CreateODBCDateTime() and the query works perfectly ok directly off the
> database....but as soon as I run it as a query of queries, I get the
> syntax error. It makes no sense. I did read a few posts about possible
> bugs in CFMX Query of Queries implementation. Query and Error is below:
>
> <cfset dateFrom = CreateODBCDateTime(CreateDate(2003, 02, 01))>
> <cfset dateTo = DateAdd('D', 7, dateFrom)>
>
> <cfquery name="NewBranchDetails" dbtype="query">
> SELECT Transaction_Date, Sum(Value_Price) as TotalSales
> FROM tempQuery
> WHERE Transaction_DATE >= #DateFrom# AND <= #DateTo#
> GROUP BY Transaction_Date
> ORDER BY Transaction_Date asc
> </cfquery>
>
> The error message I get is:
>
>
> SELECT Transaction_Date, Sum(Value_Price) as TotalSales FROM tempQuery
> WHERE Transaction_DATE >= '{ts '2003-02-01
> 00:00:00'}' GROUP BY
>
> Query Of Queries syntax error.
> Encountered "2003" at line 4, column 48.
>
> The error occurred in C:\Inetpub\wwwroot\Graphing\index.cfm: line 116
>
> 114 : GROUP BY Transaction_Date
> 115 : ORDER BY Transaction_Date asc
> 116 : </cfquery>
> 117 :
> 118 :
>
>
>
>
>
>
> Thanks,
>
> Phil
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to
> [EMAIL PROTECTED]
>
> MX Downunder AsiaPac DevCon - http://mxdu.com/
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to
> [EMAIL PROTECTED]
>
> MX Downunder AsiaPac DevCon - http://mxdu.com/
>
>
>
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
