Running a master query and a query of queries

Question

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.

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

<!--- QoQ for FIRSTCONN --->
<!--- Master Query --->
<cfquery datasource = "XX.XX.X.XX" name="master1">
SELECT DATE(Timedetail) as FIRSTCONN, COUNT(Timedetail) as FIRSTOccurances, EVENTS 
FROM MyDatabase
WHERE EVENTS = "FIRST" 
GROUP BY FIRSTCONN ;
</cfquery> 
<!--- Detail Query --->
<!--- <cfdump var = "#master#"> --->
<cfquery dbtype="query" name="detail1">
SELECT *
FROM master1 
WHERE FIRSTCONN >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"> 
AND FIRSTCONN < <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;
</cfquery> 
<!--- QoQ for SECONDCONN --->
<!--- Master Query --->
<cfquery datasource = "XX.XX.X.XX" name="master2">
SELECT DATE(Timedetail) as SECONDCONN, COUNT(Timedetail) as SECONDOccurances, EVENTS 
FROM MyDatabase
WHERE EVENTS = "SECOND" 
GROUP BY SECONDCONN ;
</cfquery> 
<!--- Detail Query --->
<!--- <cfdump var = "#master#"> --->
<cfquery dbtype="query" name="detail2">
SELECT *
FROM master2 
WHERE SECONDCONN >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"> 
AND SECONDCONN < <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;
</cfquery>

• Tan: @Matt Busche I tested the dump of master query, it's not pulling up any details for SECONDCONN. That is I can only see three columns as of now, namely, timedetail, firstOccurances and events. I belive UNION is not working
• Matt Busche: union is perfectly valid SQL. You can try with a union all but since the rows are different you shouldn't have different results. Using just union eliminates any duplicate rows and union all retains all rows

Answer 1

You could use a UNION to run one master query

<cfquery datasource = "XX.XX.X.XX" name="master1">
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;
</cfquery>

and then in your QoQ reference the correct column name

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

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

• Tan: Thanks for your answer. I have seven connections, I mean I'll have to UNION till SEVENTHCONN. Do you think that this will take less time as compared to what I have presently?
• Matt Busche: you should set it up and time each one. It should be faster.