1 Reply Latest reply on Jun 20, 2015 11:24 AM by BKBK

    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.