Problem with UNION

brettk_1 · November 9th, 2004, 07:25 AM

I have the following sql which works:

SELECT scheduleddtdesc AS REASONCODE, SUM(scheduleddthours) AS [DOWNTIME]
FROM staworkpatterns
INNER JOIN tbllines ON staworkpatterns.workcentreid = tbllines.line
INNER JOIN staarea ON tbllines.areaid = staarea.areaid
INNER JOIN stafacilitylocation ON staarea.facilityid = stafacilitylocation.facilityid
WHERE scheduleddthours <> 0
AND staFacilityLocation.FacilityID = 6474
AND staArea.AreaID = 6475
AND tblLines.Line = 'M-BP-2'
AND currentdate >= '1-Sep-2004'
AND currentdate < '10-Nov-2004'
GROUP BY scheduleddtdesc

UNION

SELECT REASONCODE, SUM(DATEDIFF(mi,StopTime,StartTime)) as [DOWNTIME]
FROM tblDowntime
INNER JOIN tblPo on tblPO.xlink = tbldowntime.xlink
WHERE DowntimeType = 'SD'
AND tbldowntime.xLink IN (SELECT xLink FROM TBLPO INNER JOIN tblLines ON tblLines.Line = tblPO.Line INNER JOIN staArea ON staArea.AreaID = tblLines.AreaID INNER JOIN staFacilityLocation ON staFacilityLocation.FacilityID = staArea.FacilityID WHERE OEERATE > 0 AND OEETIMEUNIT > 0 AND staFacilityLocation.FacilityID = 6474 AND staArea.AreaID = 6475 AND tblLines.Line = 'M-BP-2' AND date >= '1-Sep-2004'AND date < '10-Nov-2004')
GROUP BY REASONCODE
ORDER BY DOWNTIME DESC

This code produces the following:

REASONCODE DOWNTIME
---------- --------
Breaks 680.00
Breaks 8.30

My problem is that i need it to group the breaks so that I get a downtime of 688.30

Any help woulb be greatly appreciated.

Thanks.:(

happygv · November 9th, 2004, 08:08 AM

Try this.

Code:

Select ReasonCode, Sum(Downtime) 
From
(
SELECT scheduleddtdesc AS REASONCODE, SUM(scheduleddthours) AS DOWNTIME
FROM staworkpatterns
INNER JOIN tbllines ON staworkpatterns.workcentreid = tbllines.line
INNER JOIN staarea ON tbllines.areaid = staarea.areaid
INNER JOIN stafacilitylocation ON staarea.facilityid = stafacilitylocation.facilityid
WHERE scheduleddthours <> 0
AND staFacilityLocation.FacilityID = 6474
AND staArea.AreaID = 6475
AND tblLines.Line = 'M-BP-2'
AND currentdate >= '1-Sep-2004'
AND currentdate < '10-Nov-2004'
GROUP BY scheduleddtdesc

UNION

SELECT REASONCODE, SUM(DATEDIFF(mi,StopTime,StartTime)) as DOWNTIME
FROM tblDowntime
INNER JOIN tblPo on tblPO.xlink = tbldowntime.xlink
WHERE DowntimeType = 'SD'
AND tbldowntime.xLink IN (SELECT xLink FROM TBLPO INNER JOIN tblLines ON tblLines.Line = tblPO.Line INNER JOIN staArea ON staArea.AreaID = tblLines.AreaID INNER JOIN staFacilityLocation ON staFacilityLocation.FacilityID = staArea.FacilityID WHERE OEERATE > 0 AND OEETIMEUNIT > 0 AND staFacilityLocation.FacilityID = 6474 AND staArea.AreaID = 6475 AND tblLines.Line = 'M-BP-2' AND date >= '1-Sep-2004'AND date < '10-Nov-2004')
GROUP BY REASONCODE
ORDER BY DOWNTIME DESC
) as DerivedTable
Group by ReasonCode
Order by ReasonCode

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection