2 Replies Latest reply on Oct 15, 2009 10:37 AM by djkhalif

    CF8: Combining Queries

    djkhalif Level 1

      Good morning all,

       

      I Would like to combine these queries:

       

       

      <cfquery name="rsRepair" datasource="#REQUEST.datasource#">
      select DATEADD(wk, DATEDIFF(wk, 0, Record_date), 0) AS Weekly_Repair, count(e.Disposition_ID) AS Repair
      from  tbl_Assembly_holds e
      WHERE Record_date Between '#FORM.dateFrom#' and '#FORM.dateTo#' and e.Disposition_ID = '2'
      GROUP BY DATEADD(wk, DATEDIFF(wk, 0, Record_date), 0)
      ORDER BY Weekly_Repair 
      </cfquery>
      <cfquery name="rsTotalUnits" datasource="#REQUEST.datasource#">
      SELECT     DATEADD(wk, DATEDIFF(wk, 0, DateProd), 0) AS Weekly_Production, sum(UnitsProd) AS Total_Units
      FROM         tbl_Assembly_Production
      WHERE dateProd Between '#FORM.dateFROM#' and '#FORM.dateTo#'
      GROUP BY DATEADD(wk, DATEDIFF(wk, 0, DateProd), 0)
      ORDER BY Weekly_Production 
      </cfquery>
      <cfquery name="rsTotals" datasource="#REQUEST.datasource#">
      select sum(unitsprod) as Totals
      from tbl_assembly_production
      WHERE dateProd Between '#FORM.dateFROM#' and '#FORM.dateTo#'
      </cfquery>
      <cfquery name="rsRPTotals" datasource="#REQUEST.datasource#">
      select count(Disposition_ID) As rpTotals
      from tbl_assembly_holds
      WHERE Record_date Between '#FORM.dateFrom#' and '#FORM.dateTo#' and Disposition_ID = '2'
      </cfquery>

       

      Thanks,

       

       

      djkhalif

        • 1. Re: CF8: Combining Queries
          Dan Bracuk Level 5

          Sounds like your sql knowlege is limited.  That being the case, I've heard good things about the book Teach Yourself SQL in 10 Minutes by Ben Forta.

           

          The general syntax for what you are attempting is:

           

          select somefields, sum(something) thesum

          from table1 t1 join table2 t2 on t1.fieldname = t2.fieldname

          where whatever

          group by somefields

          • 2. Re: CF8: Combining Queries
            djkhalif Level 1

            Dan,

             

            I have that book. Thanks for the input.

             

             

            Ex.

            SELECT     DATEADD(wk, DATEDIFF(wk, 0, p.DateProd), 0) AS Weekly_Production, COUNT(e.Disposition_ID) AS Repair, SUM(p.UnitsProd) AS Total_Units
            FROM         tbl_Assembly_Holds AS e INNER JOIN
                                  tbl_Assembly_Production AS p ON p.WorkOrder = e.WorkOrder
            WHERE     (e.Record_Date BETWEEN '9/7/2009' AND '9/28/2009') AND (e.Disposition_ID = '2') AND (p.DateProd BETWEEN '9/7/2009' AND '9/28/2009')
            GROUP BY DATEADD(wk, DATEDIFF(wk, 0, p.DateProd), 0)
            ORDER BY Weekly_Production