Skip to main content
D
djkhalif
Inspiring
October 15, 2009
Question

CF8: Combining Queries

  • October 15, 2009
  • 1 reply
  • 748 views

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

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
October 15, 2009

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

D
djkhalifAuthor
Inspiring
October 15, 2009

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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices