Query Of Queries

Question

I'm having some trouble with Query of Queries in CF5. I have included my select statement in the attached code.

What I'm looking to be able to do now is select totals from the original query using a Query of Queries. For example, I need to know my total return visitors as compared to my total new visitors (for the sake of the query, a new visitor is anyone whose FirstVisitDate falls within whatever date range the user has specified [attributes.start to attributes.end] and a return visitor is anyone whose FirstVisitDate does nto fall within the specified range.

I have tried something like:
<cfquery dbtype="query" name="GetNewHits">
Select Count(Visitor)
From QueryVisitors
Where FirstVisit
Between #attributes.start#
and #attributes.end#
</cfquery>

The above query (when it returns results at all) returns a count of all of the records within the original query (to me it seems like it doesn't recognize the field FirstVisit as a date field). Can anyone offer any suggestions? Thanks,

--Daniel Casper

Anonymous · Accepted Answer

I wish I had a choice to upgrade, but the company won't spring for a local server and our hosting company is 2 releases behind. I think I found a way to implement it though without using Q of Q.

I put a conditional increment in when the results are displayed that checks the date against my user range and then either increments a newhit variable if it falls within the range, or increments a returnhit variable if it does not. So far it's working well that way. If anyone needs it, the conditional increment is:

<cfloop query="QueryVisitors">
<cfif FirstVisit gt #dateformat(attributes.start, "YYYY-MM-DD")#><cfset newhits = #newhitscur# + 1><cfelse><cfset returnhits = #returnhitscur# + 1></cfif>
</cfloop>

Thanks all for your suggestions.

Anonymous · Answer

I (and many others) don't support CF5 anymore, so my first suggestion is to upgrade.

Anyway, I don't remember enough of the Q of Q problems in CF5 but since you are using MS SQL, there is another way.

First add this to the select statement of QueryVisitors:

, CASE
dbo.WebSiteVisitors.FirstVisitDate
BETWEEN
#CreateODBCDate(attributes.start)#
AND
#CreateODBCDate(attributes.end)#
THEN
1
ELSE
0
END AS bFirstVisit

Then your Q of Q becomes:

<CFQUERY dbtype="query" name="GetNewHits">
Select SUM (bFirstVisit) AS iNumNewVisits
From QueryVisitors
</CFQUERY>

-- which should work even in CF5.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.