Highlighted

Best way to handle output from multiple queries?

New Here ,
Jun 17, 2015

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.

TOPICS
Advanced techniques

Views

404

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Best way to handle output from multiple queries?

New Here ,
Jun 17, 2015

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.

TOPICS
Advanced techniques

Views

405

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 17, 2015 0
BKBK LATEST
Adobe Community Professional ,
Jun 20, 2015

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 20, 2015 0