Best way to handle output from multiple queries?

[seasonedweb]

I have to create several reports who's data will be displayed like this:

TYPE	# COMPLETED	TURN TIME
Multi-Family (MF)
Multi – Family FHA
Single Family (SF)
Single Family FHA
Condo
Land
Total/Average:	Total:	Average:

It seems I would need to query separately for each "TYPE"? Everything I tried outputs Cartesian products -

The action page from the date search (BUSINESS DAYS ELAPSED) form:

<cfset Session.StartDate = "#CreateODBCDate(FORM.StartDate)#">

<cfset Session.EndDate = "#CreateODBCDate(FORM.EndDate)#">

<cfscript>

function businessDaysBetween(date1,date2) {

var numberOfDays = 0;

while (date1 LT date2) {

date1 = dateAdd("d",1,date1);

if(dayOfWeek(date1) GTE 2 AND dayOfWeek(date1) LTE 6) numberOfDays = incrementValue(numberOfDays);

}

return numberOfDays;

}

</cfscript>

HOME SALES (order_type_ID = 1):

<cfquery name="client_report_sales" datasource="#Request.BaseDSN#">

SELECT order_ID, order_property_type_ID, order_order_type_ID, order_status_ID, order_create_date, order_inspection_comp_date, order_type_ID, property_type_ID, property_type

FROM main_orders, lookup_order_type, lookup_property_type

WHERE order_status_ID = 9 (inspection completed)

OR order_status_ID = 10 (order invoiced)

AND order_create_date = #Session.StartDate#

AND order_inspection_comp_date = #Session.EndDate#

AND  order_type_ID = 1

AND order_property_type_ID = (??)

</cfquery>

NUMBER COMPLETED: #client_report_sales.RecordCount#

TURN TIME:

<cfoutput query="client_report_sales_MF">

<cfif isDate(order_create_date) and isDate(order_inspection_comp_date)>

<cfset dt1=parseDateTime(order_create_date)>

<cfset dt2=parseDateTime(order_inspection_comp_date)>

TABULAR FORMAT: ?? Would this be better outputted as an Excel Spreadsheet?

CF 9 / SQL Server

Thank you in advance fro any help with this.

Norman B.

[BKBK]

1) Where does the function businessDaysBetween() get called? If it doesn't, why do you include it?

2) Your model confused me. Your initial table has a column named, generically, "Type". Whereas your query implies there are at least 2 Type types, order_type and order_property_type.