How can I CFLoop a query in another query correctly?

Question

Background/Purpose: I am creating a coldfusion document that contains SQL for grabbing values from my company's database. I am finding the conversion rate (Licenses Sold / Registrations) per Sales Rep on our team. Each Rep has an ID ( RegionalDirectorID) tied to users to help keep track.

Question/Problem: The problem is that we have other workers that sell licenses here or there, such as our CEO, Head Developer etc. We have 8 sales reps with the UserType of 8. I am using another query to select those UserTypes, to differentiate from other people so that our data doesn't get mixed up selecting them too.

As you can see below, I am using a cfloop to loop thru the getUsers query, specifically to help the line(Users.RegionalDirectorID = #getUsers.UserID#) print out all of the ID's of our sales reps. When I don't have the cfloop entered, I only get one row showing one sales rep. If I do have the cfloop, I will get the last sales rep.

Code:

<cfset myQuery = QueryNew("ID, ConversionRate")> 

<cfquery name="getUsers" datasource="#dsn#">
    Select UserID FROM USERS WHERE 
    UserTypeID = 8
    AND ISACTIVE = 1
</cfquery> 

<cfquery name="getREGRD" datasource="#dsn#">
 Select COUNT(DISTINCT(Users.UserID))  AS TOTALREG, RegionalDirectorID
 FROM Users
 WHERE UserTypeID = 3
<!---   <cfloop query="getUsers">
    AND (Users.RegionalDirectorID = #getUsers.UserID#)
    </cfloop>--->
AND (PARENTID IS NULL OR PARENTID = 0)
        <cfif len(selectMonth)>
            AND MONTH(Users.DateStamp) = #selectMonth#
        <cfelse>
            AND MONTH(Users.DateStamp) = #MONTH(NOW())#
        </cfif>
        GROUP BY Users.RegionalDirectorID
</cfquery> 

<cfquery name="getREGRDSold" datasource="#dsn#">
    Select COUNT(DISTINCT(UserTractLicense.UserID))  AS TOTALSOLD, Users.RegionalDirectorID
    FROM  Users, UserTractLicense 
    WHERE Users.UserID = UserTractLicense.UserID 
    AND   Users.UserTypeID = 3 AND
    (PARENTID IS NULL OR PARENTID = 0)
    <cfif len(selectMonth)>
            AND MONTH(Users.DateStamp) = #selectMonth#
    <cfelse>
            AND MONTH(Users.DateStamp) = #MONTH(NOW())#
    </cfif>
    GROUP BY Users.RegionalDirectorID
</cfquery> 

<!---<cfset newRow = QueryAddRow(myQuery, #getREGRD.RecordCount#)>
    <cfloop query="getREGRD">
        <cfset QuerySetCell(myQuery, "ID",  #getREGRD.RegionalDirectorID#, getREGRD.currentRow) />
    </cfloop>   
    <cfloop query="getREGRDSold">
        <cfset QuerySetCell(myQuery, "ConversionRate", #getREGRDSold.TOTALSOLD#/#getREGRD.TOTALREG#, getREGRDSold.currentRow) /> 
    </cfloop>
--->

<!---<cfdump var="#myQuery#">--->
<cfdump var="#getREGRD#">
<cfdump var="#getREGRDSold#">

<cfoutput query="getREGRDSold">
    #getREGRDSold.RegionalDirectorID#
</cfoutput>

The cfloop was causing the newQuery to shoot out the error below. That is why some of it is commented out:

An error occurred while evaluating the QueryAddRow function: Parameter> 2, 0, of the QueryAddRow function must be a positive integer.
The error occurred on line 70.

The resulting data has some RegionalDirectorID's that do not have the UserTypeID of 8, such as the NULL cells, which I would like to remove from the results of the query.

Answer 1

It sounds like all you need is a single JOIN, instead of all the extra looping and queries.

If the Users table stores both customers and sales reps, you will need a self join. In other words, join the Users table to itself by using aliases. Then filter each one on the appropriate user types. Something like this:

SELECT  cust.RegionalDirectorID
        , COUNT(DISTINCT(lic.UserID))  AS TOTALSOLD
FROM    Users cust
           INNER JOIN Users sales ON sales.UserID = cust.RegionalDirectorID
           INNER JOIN UserTractLicense lic ON lic.UserID = cust.UserID
<!--- customers --->
WHERE   cust.UserTypeID = 3 
<!--- sales reps --->
AND     sales.UserTypeID = 8
... etcetera 
GROUP BY cust.RegionalDirectorID

A few other comments:

1. While not always technically required, it is a good practice to fully qualify all column names whenever a query involves multiple tables.

2. Along the same lines, be sure to scope all variables. For example, use FORM.selectMonth or URL.selectMonth instead of just selectMonth.

3. Always use cfqueryparam on all variable query parameters. The most important reason is to protect your database from sql injection. It also helps improves performance for queries executed multiple times.

MONTH(Users.DateStamp) = #selectMonth#

4. I noticed the query only filters on month number (not month and year). So if selectedMonth = 8, the query will return results for the month of "August" - in any year. Was that your intention? If not, you will obviously want add a year filter as well. However, using functions on an indexed column usually prevents the database from utilizing indexes. So you might consider using this paradigm which is more index friendly:

    WHERE  TheDateColumn >= TheStartDateAtMidnight         
    AND    TheDateColumn < TheDayAfterEndDateAtMidnight