[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Calculate percentage of columns.

aiu4840

3/23/2007 10:45:00 PM

Hello,

I have the following 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 created by the following two queries:
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'
-- AND MT.TypeOfHours = 3

GROUP BY
BusinessUnit_ID,
BusinessUnitDescription



Would like to see result table like this

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%
2 Answers

Mr Tea

3/23/2007 11:08:00 PM

0

This is untested, but it should do what you are after...

SELECT B.BusinessUnit_ID + '-' + B.BusinessUnitDescription as
BusinessUnitDescription,
SUM(CASE WHEN MT.TransactionType = 'DIRECT' THEN
(ActualMinutes * Crewsize)/60 ELSE 0 END) AS
TotalDIRECTRunLaborHours,
SUM((ActualMinutes * Crewsize)/60) AS
TotalRunLaborHours,
100*SUM(CASE WHEN MT.TransactionType = 'DIRECT' THEN
(ActualMinutes * Crewsize)/60 ELSE 0 END)/NullIf(SUM((ActualMinutes *
Crewsize)/60),0) AS
[% of DIRECT run labor hours]
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


Regards
Mr Tea

"aiu4840" <khongdoc@earthlink.net> wrote in message
news:1174689882.488382.77880@l75g2000hse.googlegroups.com...
> Hello,
>
> I have the following 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 created by the following two queries:
> 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'
> -- AND MT.TypeOfHours = 3
>
> GROUP BY
> BusinessUnit_ID,
> BusinessUnitDescription
>
>
>
> Would like to see result table like this
>
> 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%
>


TheSQLGuru

3/23/2007 11:08:00 PM

0

The quick, simple, and inefficient way is to output the results from each
section of code into table variables with two fields: BUDescription (which
would look like this '20410-Food Bar Weighup', 598.5). Then join the two
together on BUDescription and output DirectHours/TotalHours.

Once you have this done, you could refactor it to remove the table variables
and simply use two derived tables and get one statement to give you the
output.

--
TheSQLGuru
President
Indicium Resources, Inc.

"aiu4840" <khongdoc@earthlink.net> wrote in message
news:1174689882.488382.77880@l75g2000hse.googlegroups.com...
> Hello,
>
> I have the following 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 created by the following two queries:
> 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'
> -- AND MT.TypeOfHours = 3
>
> GROUP BY
> BusinessUnit_ID,
> BusinessUnitDescription
>
>
>
> Would like to see result table like this
>
> 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%
>