How to loop a query and update the where clause.

Running queries w/in a loop should be avoided if possible. Do you really need to loop? Why not just feed in a list of car names and get the counts for all in single query, ie:

CarName  | NumOfCars
Car AAAA | 22
Car BBB    | 43
Car BBB    | 32
....

Something like ...

SELECT CarName, COUNT(CarNumber)  AS NumOfCars
FROM   dbo.tblCars
WHERE IncidentDate BETWEEN {date1} AND {date2}
AND      CarName IN
(
<cfqueryparam value="#ListOfNames#" list="true" cfsqltype="cf_sql_varchar">
)

I displayed a very abbreviated example but that may work. How would I build the "ListOfNames"? I haven't done this type of ColdFusion coding in quite a while.

Thank you.

Depends. What's the source of the car names? If it's from a query, just use:

<cfset listOfNames = ValueList(yourQueryName.columnName)>
... feed into other query ...

I'm sorry. the example I gave won't work with that solution and it is probably my fault. I was hoping a loop would work. My query looks something like this. I am trying to get the list of values for each car name. It's a query with subqueries.

<cfquery .....>
SELECT 
  (SELECT COUNT(CarNumber) 
	FROM  tblCars
	WHERE (tblCars.Created BETWEEN '1/1/2014' AND '3/1/14' )
	
		   AND      CarName IN  (<cfqueryparam value="#ListOfNames#" list="true" cfsqltype="cf_sql_varchar">)
 ) as CreatedCars, 



  (SELECT COUNT(CarNumber)
    FROM  tblCars
    WHERE (tblCars.Created BETWEEN '1/1/2014' AND '3/1/14') 
	AND	(tblCars.Status = 'Closed')
	AND (ChangePerson NOT IN ('blah', 'blah2'))
   
		   AND      CarName IN  (<cfqueryparam value="#ListOfNames#" list="true" cfsqltype="cf_sql_varchar">)
) as ClosedCars, 
				
  (SELECT COUNT(tblCars.CarNumber) 
	FROM   tblCars INNER JOIN
                      tblCarsIncidents ON tblCars.ID2 = tblCarsIncidents.ID2
	WHERE (tblCars.Created BETWEEN '1/1/2014' AND '3/1/14') 
	 AND (tblCars.Status = 'Closed') AND (tblCarsIncidents = 'Red') AND tblCarsIncidents.Status = 'Closed'
		
		   AND      CarName IN  (<cfqueryparam value="#ListOfNames#" list="true" cfsqltype="cf_sql_varchar">)
     ) as ClosedCarsRed
</cfquery>

Select allOpen in new window

So, I'd like to output the query and show a list of Carnames and then numbers for each like this:

CarName     CreatedCars     Closed Cars     ClosedCarsRed
--------------------------------------------------------------------------
CoolCar              5                       7                       4
NeatCar              4                       4                       8
WowCar              4                      8                       10

Ooops, our posts clashed.  I think I understand now.  Let me look over the subqueries..

(Edit fix syntax error).

Looks like all of the subqueries are filtered on the same createdDate range. Try this smaller query first (not tested).  Does it return the right numbers? Then we can add in the incidents table subquery.

SELECT c.CarName
      , COUNT(c.CarNumber) AS CreatedCars
      , SUM(CASE
                WHEN c.Status = 'Closed'  AND c.ChangePerson NOT IN ('blah', 'blah2')  THEN 1
                ELSE 0
              END
        ) as ClosedCars
FROM   tblCars c
WHERE  c.Created BETWEEN '1/1/2014' AND '3/1/14'
GROUP BY c.CarName

In my example I only used 3 total queries but I have about ten and most of them have joins like the one you omitted.
I can't test until tomorrow but that looks good so far.
Thank you.

Could I put my original query in a CFC and then send the carname as in invoke argument in a loop somehow?

Yeah, you can put it in a CFC, but so far, I don't think you need a loop.  If you simply filter the outer query on the car names you want , ie "CoolCar,NeatCar,WowCar" .... the results will contain the totals for each of those car names:

<!--- obviously use cfqueryparam in the real query --->
SELECT c.CarName
      , COUNT(c.CarNumber) AS CreatedCars
      , SUM(CASE
                WHEN c.Status = 'Closed'  AND c.ChangePerson NOT IN ('blah', 'blah2')  THEN 1
                ELSE 0
              END
        ) as ClosedCars
FROM   tblCars c
WHERE  c.Created BETWEEN '1/1/2014' AND '3/1/14'
AND       c.CarName IN ( #ListOfCarNamesYouWant# )  <=== add list as a filter
GROUP BY c.CarName

For tomorrow, assuming the base query works, something like this should do it for the subquery. Note, I think the earlier example omitted the name of the color column, so be sure to update it in the query:

SELECT c.CarName
      , COUNT(c.CarNumber) AS CreatedCars
      , SUM(CASE
                WHEN c.Status = 'Closed'  AND c.ChangePerson NOT IN ('blah', 'blah2')  THEN 1
                ELSE 0
              END
        ) AS ClosedCars
      , (
         SELECT COUNT(*)
           FROM    tblCarsIncidents i
           WHERE  i.ID2 = c.ID2
           AND      i.Status = 'Closed'
           AND      i.Status = c.Status
           AND      i.MissingColumnName = 'Red'
      )
        AS ClosedCarsRed
FROM   tblCars c
WHERE  c.Created BETWEEN '1/1/2014' AND '3/1/14'
AND       c.CarName IN
(
<cfqueryparam value="#ListOfCarNamesYouWant#" list="true"
                cfsqltype="(your type here)...">
)
GROUP BY c.CarName

I have one more obstacle with this method. When I output  the query for each row I need to add columns for each CarName that will have some calculations of the other columns. Will that be possible?

Probably.  All depends on what the calculations are ... if it's some sort of summary of the counts for example, you might just want to do that in CF code.  ie

CarName     CreatedCars     Closed Cars     ClosedCarsRed            Total
------------------------------------------------------------------------------------
CoolCar              5                       7                       4                     16
NeatCar              4                       4                       8                     16
WowCar              4                      8                       10                    22

I am putting percentages and other calculations next to the other numbers. for example:

CarName     CreatedCars     Closed Cars     ClosedCarsRed            Total
------------------------------------------------------------------------------------
CoolCar              10                       7                       7   (75%)             24
NeatCar              4                       4                       8                          16
WowCar              4                      8                       10                         22

I think I have it working by looping over the cfinvoke. Using your method, I'm not sure how I would add other hard-coded columns and other numbers in the existing columns since the rest of the output is coming from the query alone. You answer my original question; I should have provided much more detail.