CF8: Combining Queries

[djkhalif]

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

[Dan_Bracuk]

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

[djkhalif]

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