<cfquery name="findQtrs" dbtype="query">
SELECT DISTINCT DATEADD(qq, DATEDIFF(qq, 0, event_datestart), 0) as SQtr, DATEADD(qq, DATEDIFF(qq, -1, event_datestart), -1)as EQtr
FROM eventData
ORDER BY SQtr ASC
</cfquery>when I run the above I get error in syntax of '(' looking for a from. However, when I run that exact query in SQL Server, I get the results I want and need. Does a query have to be set up differently when it is a query of query?
Thanks!
Not all of MS SQL's functions are available in a Query of Query. I'm guessing it just doesn't recognize the DateAdd or DateDiff functions.
Maybe try the MS SQL equivalent of a View?
Yeah, QoQ is very limited. If it had functions to do what you're doing, it would probably have different function names, since those are TSQL-specific.
Check out Adobe's CF docs for QoQ usage: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=using_recordsets_7.html
Does a query have to be set up differently when it is a query of query?
Yes.
You can't use regular CF functions inside a QoQ. It's not a database query, so it's internal syntax is very limited. Only a handful of functions are implemented. None of which will help in your case.
If you need that functionality you'll have to 1) use date functions it in the original database query or 2) loop through the query, record by record, and use CF's date functions on the values (obviously a bad option).
As a general rule, I would leave as much data crunching work to be done as possible on the database server as it will be much more efficient.
In the situation you described, it might be better to create a "View" in the database based on your above SQL and then use a simple query to pull data out of that.
As a general rule, I would leave as much data crunching work to be done as possible on the database server as it will be much more efficient.
Like I said, in this case they have almost no choice. Since it can't be done in a QoQ ;-)
In the situation you described, it might be better to create a "View" in the database based on your above SQL and then use a simple query to pull data out of that.
Maybe. Views tend to be useful when the same calculated information is used in multiple places. If something is only used on once, or one page only, it's generally not worth it to create a view just for that purpose.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.