Codeman
3/24/2007 2:19:00 AM
"aiu4840" wrote:
> Hello,
>
> I have the following two result sets:
> 1.
> BusinessUnitDescription TotalDIRECTRunLaborHours
> 20410-Food Bar Weighup 598.500000
> 20420-Food Bar Pilot 2172.000000
> 20430-Food Bar 463.500000
> 20480-Food Bar Hand Assembly 97.500000
>
> 2.
> BusinessUnitDescription TotalRunLaborHours
> 20410-Food Bar Weighup 625.500000
> 20420-Food Bar Pilot 2832.500000
> 20430-Food Bar 517.500000
> 20480-Food Bar Hand Assembly 106.000000
>
> which were generated by the following two queries, respectively:
>
> 1.
>
> SELECT B.BusinessUnit_ID + '-' + B.BusinessUnitDescription as
> BusinessUnitDescription,
> SUM((ActualMinutes * Crewsize)/60) AS
> TotalDIRECTRunLaborHours
> FROM GLB_BusinessUnit_MASTER B
> JOIN GLB_Plant_Master P ON B.DepartmentNumber_ID =
> P.DepartmentNumber_ID
> INNER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
> MT.BusinessUnit
>
> WHERE (P.PlantDescription = 'Food Bar' OR 'Food Bar' = '(All)')
> AND MT.TransactionType IN ('DIRECT')
> AND MT.TransactionDateTime >= '03/01/2007' AND
> MT.TransactionDateTime <= '03/31/2007'
> GROUP BY
> BusinessUnit_ID,
> BusinessUnitDescription
>
> 2.
> SELECT
> B.BusinessUnit_ID + '-' + B.BusinessUnitDescription as
> BusinessUnitDescription,
> SUM((ActualMinutes * Crewsize)/60) AS
> TotalRunLaborHours
> FROM GLB_BusinessUnit_MASTER B
> JOIN GLB_Plant_Master P ON B.DepartmentNumber_ID =
> P.DepartmentNumber_ID
> INNER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
> MT.BusinessUnit
>
> WHERE (P.PlantDescription = 'Food Bar' OR 'Food Bar' = '(All)')
> AND MT.TransactionType IN ('DIRECT','SETUP','INDIRECT')
> AND MT.TransactionDateTime >= '03/01/2007' AND
> MT.TransactionDateTime <= '03/31/2007'
> GROUP BY
> BusinessUnit_ID,
> BusinessUnitDescription
>
>
> For the past two days, I had unsuccesfully tried to get to the
> following result set:
>
> BusinessUnitDescription % of DIRECT run labor hours
> 20410-Food Bar Weighup 96%
> 20420-Food Bar Pilot 77%
> 20430-Food Bar 90%
> 20480-Food Bar Hand Assembly 92%
>
> I'd appreciate any help/suggestion you can provide.
>
> Note: 96% = 598.5/625.5, 77% = 2172/2832, 90% = 463.5/517.5, and 92% =
> 97.5/106
>
> Thanks for your help.
>
> Dien.
>
>
Since you already have the two queries, why don't you try this
Select
one.BusinessUnitDescription,
one.TotalDIRECTRunLaborHours/two.TotalRunLaborHours As [% of DIRECT run
labor hours]
From
(
SELECT
B.BusinessUnit_ID + '-' + B.BusinessUnitDescription as
BusinessUnitDescription,
SUM((ActualMinutes * Crewsize)/60) AS
TotalDIRECTRunLaborHours
FROM GLB_BusinessUnit_MASTER B
JOIN GLB_Plant_Master P ON B.DepartmentNumber_ID =
P.DepartmentNumber_ID
INNER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
MT.BusinessUnit
WHERE (P.PlantDescription = 'Food Bar' OR 'Food Bar' = '(All)')
AND MT.TransactionType IN ('DIRECT')
AND MT.TransactionDateTime >= '03/01/2007' AND
MT.TransactionDateTime <= '03/31/2007'
GROUP BY
BusinessUnit_ID,
BusinessUnitDescription
) one
Inner Join
(
SELECT
B.BusinessUnit_ID + '-' + B.BusinessUnitDescription as
BusinessUnitDescription,
SUM((ActualMinutes * Crewsize)/60) AS
TotalRunLaborHours
FROM GLB_BusinessUnit_MASTER B
JOIN GLB_Plant_Master P ON B.DepartmentNumber_ID =
P.DepartmentNumber_ID
INNER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
MT.BusinessUnit
WHERE (P.PlantDescription = 'Food Bar' OR 'Food Bar' = '(All)')
AND MT.TransactionType IN ('DIRECT','SETUP','INDIRECT')
AND MT.TransactionDateTime >= '03/01/2007' AND
MT.TransactionDateTime <= '03/31/2007'
GROUP BY
BusinessUnit_ID,
BusinessUnitDescription
) two
On one.BusinessUnitDescription = two.BusinessUnitDescription