I am trying to correct a statement where two tables do not have any unique information ...
BIQUERY.TBLNEWOEE OEE and BIQUERY.TBLDTIME DT. I am trying to use a second subquery to alleviate the problem but I am not able to do so. What is the best way to segreate the BIQUERY.TBLDTIME DT in a subquery to add the Downtime column without having outrageous totals in the columns? Downtime is the only column contained in the BIQUERY.TBLDTIME DT table. The select statement is as follows...
SELECT TMP.*, AVAILABILITY * PERFORMANCEEFFICIENCY * QUALITYRATE AS OEE
FROM (SELECT OEE.MACHINENO, MACH.MACHINEDESCRIPTION, CASE WHEN IFNULL(SUM(NETTIME), 0) = 0 THEN 0 ELSE FLOAT(SUM(OPTIME)) / FLOAT(SUM(NETTIME)) END AS Availability, CASE WHEN IFNULL(SUM(OPTIME), 0) = 0 THEN 0 ELSE float(SUM(CYCTIME * PARTS)) / FLOAT(SUM(OPTIME)) END AS PerformanceEfficiency, CASE WHEN IFNULL(SUM(PARTS), 0) = 0 THEN 0 ELSE FLOAT((SUM(PARTS) - SUM(DEFECTS))) / FLOAT(SUM(PARTS)) END AS QualityRate, SUM(OEE.PARTS) AS TotalParts, SUM(DT.DTHOURS) + (FLOAT(SUM(DTMINS)) / 60) AS Downtime, SUM(DEFECTS) AS Scrap
FROM BIQUERY.TBLNEWOEE OEE, BIQUERY.VWPRODUCTIONMACHINES MACH, BIQUERY.TBLDTIME DT
WHERE OEE.COMPANY = MACH.COMPANY AND OEE.LOCATION = MACH.LOCATION AND OEE.MACHINENO = MACH.MACHINENUMBER AND OEE.COMPANY = DT.COMPANY AND OEE.LOCATION = DT.LOCATION AND OEE.MACHINENO = DT.MACHINENO AND(OEE.THEDATE BETWEEN ? AND ?) AND (OEE.MACHINENO BETWEEN ? AND ?)
GROUP BY OEE.MACHINENO, MACH.MACHINEDESCRIPTION) TMP
ORDER BY MACHINENO
BIQUERY.TBLNEWOEE OEE and BIQUERY.TBLDTIME DT. I am trying to use a second subquery to alleviate the problem but I am not able to do so. What is the best way to segreate the BIQUERY.TBLDTIME DT in a subquery to add the Downtime column without having outrageous totals in the columns? Downtime is the only column contained in the BIQUERY.TBLDTIME DT table. The select statement is as follows...
SELECT TMP.*, AVAILABILITY * PERFORMANCEEFFICIENCY * QUALITYRATE AS OEE
FROM (SELECT OEE.MACHINENO, MACH.MACHINEDESCRIPTION, CASE WHEN IFNULL(SUM(NETTIME), 0) = 0 THEN 0 ELSE FLOAT(SUM(OPTIME)) / FLOAT(SUM(NETTIME)) END AS Availability, CASE WHEN IFNULL(SUM(OPTIME), 0) = 0 THEN 0 ELSE float(SUM(CYCTIME * PARTS)) / FLOAT(SUM(OPTIME)) END AS PerformanceEfficiency, CASE WHEN IFNULL(SUM(PARTS), 0) = 0 THEN 0 ELSE FLOAT((SUM(PARTS) - SUM(DEFECTS))) / FLOAT(SUM(PARTS)) END AS QualityRate, SUM(OEE.PARTS) AS TotalParts, SUM(DT.DTHOURS) + (FLOAT(SUM(DTMINS)) / 60) AS Downtime, SUM(DEFECTS) AS Scrap
FROM BIQUERY.TBLNEWOEE OEE, BIQUERY.VWPRODUCTIONMACHINES MACH, BIQUERY.TBLDTIME DT
WHERE OEE.COMPANY = MACH.COMPANY AND OEE.LOCATION = MACH.LOCATION AND OEE.MACHINENO = MACH.MACHINENUMBER AND OEE.COMPANY = DT.COMPANY AND OEE.LOCATION = DT.LOCATION AND OEE.MACHINENO = DT.MACHINENO AND(OEE.THEDATE BETWEEN ? AND ?) AND (OEE.MACHINENO BETWEEN ? AND ?)
GROUP BY OEE.MACHINENO, MACH.MACHINEDESCRIPTION) TMP
ORDER BY MACHINENO