Running a master query and a query of queries The following is code I'm running to FIRST, SECOND, THIRD and so on until SEVENTH with a master query and a query of query.

As it's clear that I'm using the master query for each of FIRSTCONN, SECONDCONN etc., I want to have only one master query and the rest subqueries.


Is this an efficient way to do so?
How can I make my code efficient?




&lt;!--- QoQ for FIRSTCONN ---&gt;
&lt;!--- Master Query ---&gt;
&lt;cfquery datasource = "XX.XX.X.XX" name="master1"&gt;
SELECT DATE(Timedetail) as FIRSTCONN, COUNT(Timedetail) as FIRSTOccurances, EVENTS 
FROM  MyDatabase
WHERE EVENTS = "FIRST" 
GROUP BY FIRSTCONN ;
&lt;/cfquery&gt; 

&lt;!--- Detail Query ---&gt;
&lt;!--- &lt;cfdump var = "#master#"&gt; ---&gt;
&lt;cfquery dbtype="query" name="detail1"&gt;
SELECT  *
FROM master1 
WHERE FIRSTCONN  &gt;= &lt;cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"&gt; 
AND FIRSTCONN  &lt;  &lt;cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar"&gt;;
&lt;/cfquery&gt;  

&lt;!--- QoQ for SECONDCONN ---&gt;
&lt;!--- Master Query ---&gt;
&lt;cfquery datasource = "XX.XX.X.XX" name="master2"&gt;
SELECT DATE(Timedetail) as SECONDCONN, COUNT(Timedetail) as SECONDOccurances, EVENTS 
FROM  MyDatabase
WHERE EVENTS = "SECOND" 
GROUP BY SECONDCONN ;
&lt;/cfquery&gt; 

&lt;!--- Detail Query ---&gt;
&lt;!--- &lt;cfdump var = "#master#"&gt; ---&gt;
&lt;cfquery dbtype="query" name="detail2"&gt;
SELECT  *
FROM   master2 
WHERE  SECONDCONN  &gt;= &lt;cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"&gt; 
AND    SECONDCONN  &lt;  &lt;cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar"&gt;;
&lt;/cfquery&gt;
You could use a UNION to run one master query

&lt;cfquery datasource = "XX.XX.X.XX" name="master1"&gt;
SELECT DATE(Timedetail) as FIRSTCONN, COUNT(Timedetail) as FIRSTOccurances, EVENTS 
FROM  MyDatabase
WHERE EVENTS = "FIRST" 
GROUP BY FIRSTCONN
UNION
SELECT DATE(Timedetail) as SECONDCONN, COUNT(Timedetail) as SECONDOccurances, EVENTS 
FROM  MyDatabase
WHERE EVENTS = "SECOND" 
GROUP BY SECONDCONN;
&lt;/cfquery&gt;


and then in your QoQ reference the correct column name

&lt;cfquery dbtype="query" name="detail1"&gt;
SELECT  *
FROM master1 
WHERE FIRSTCONN  &gt;= &lt;cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"&gt; 
AND FIRSTCONN  &lt;  &lt;cfqueryparam value="#dateAdd('d', 1,form.enddate)#"     cfsqltype="cf_sql_varchar"&gt;
AND EVENTS = 'FIRST';




&lt;cfquery dbtype="query" name="detail2"&gt;
SELECT  *
FROM   master1
WHERE  SECONDCONN  &gt;= &lt;cfqueryparam value="#form.startdate#"     cfsqltype="cf_sql_varchar"&gt; 
AND    SECONDCONN  &lt;  &lt;cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar"&gt;
AND EVENTS = 'SECOND';
&lt;/cfquery&gt;