Help with calculation

Can someone tell me how to divide the results of the two "types" in this query?
I need the result to be Terminated/Budgeted.  Right now, I get this:
51314.81      Budgeted
192      Terminated

I another row to show .04% as the percentage.
SELECT  SUM(ISNULL(ApprovedHrsPerPeriod, 0)) AS Hours, 'Budgeted' AS TYPE
FROM     PpPositionDictionary
WHERE  (Payroll = 'reg') AND (Active = 'y')
UNION
SELECT  SUM(ISNULL(HoursPerPeriod, 0)) AS Hours, 'Terminated' AS TYPE
FROM     PPPEREmpPositionFields
WHERE  (StatusDate BETWEEN @From AND @Thru) AND (Status = 'TERM') AND (PositionNumber LIKE '%B')

Open in new window

palmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Patrick MatthewsCommented:
SELECT  SUM(ISNULL(ApprovedHrsPerPeriod, 0)) AS Hours, 'Budgeted' AS TYPE
FROM     PpPositionDictionary
WHERE  (Payroll = 'reg') AND (Active = 'y')
UNION
SELECT  SUM(ISNULL(HoursPerPeriod, 0)) AS Hours, 'Terminated' AS TYPE
FROM     PPPEREmpPositionFields
WHERE  (StatusDate BETWEEN @From AND @Thru) AND (Status = 'TERM') AND (PositionNumber LIKE '%B')
UNION
(SELECT  SUM(ISNULL(ApprovedHrsPerPeriod, 0))
FROM     PpPositionDictionary
WHERE  (Payroll = 'reg') AND (Active = 'y')) /
(SELECT  SUM(ISNULL(HoursPerPeriod, 0)) AS Hours
FROM     PPPEREmpPositionFields
WHERE  (StatusDate BETWEEN @From AND @Thru) AND (Status = 'TERM') AND (PositionNumber LIKE '%B')) AS Hours, 'Perc' AS Type
0
 
JestersGrindCommented:
I think that you want to do something like this, instead of union.  Just make sure both values are decimal, not integer.
Greg

SELECT 
(SELECT  SUM(ISNULL(HoursPerPeriod, 0)) AS Hours, 'Terminated' AS TYPE
FROM     PPPEREmpPositionFields
WHERE  (StatusDate BETWEEN @From AND @Thru) AND (Status = 'TERM') AND (PositionNumber LIKE '%B')) /
(SELECT  SUM(ISNULL(ApprovedHrsPerPeriod, 0)) AS Hours, 'Budgeted' AS TYPE
FROM     PpPositionDictionary
WHERE  (Payroll = 'reg') AND (Active = 'y')) AS Percentage

Open in new window

0
 
palmer9Author Commented:
matthewspatrick -tried this one and get an error : Unable to parse query text...
Incorrect syntax near '/'
JestersGrind - get this error:  Only one expression can be specified in the select list when the subquery is not instroduced with EXISTS.
I am writing in Visual Studio 2005 - not sure I can use EXISTS there???
0
 
JestersGrindCommented:
Oops!  Try this.
Greg
 
 

SELECT 
(SELECT  SUM(ISNULL(HoursPerPeriod, 0))
FROM     PPPEREmpPositionFields
WHERE  (StatusDate BETWEEN @From AND @Thru) AND (Status = 'TERM') AND (PositionNumber LIKE '%B')) /
(SELECT  SUM(ISNULL(ApprovedHrsPerPeriod, 0))
FROM     PpPositionDictionary
WHERE  (Payroll = 'reg') AND (Active = 'y')) AS Percentage
 

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
palmer9Author Commented:
Perfect!  Thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.