Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.