Solved

Help with calculation

Posted on 2009-04-07
5
145 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:palmer9
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 
LVL 21

Expert Comment

by:JestersGrind
Comment Utility
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
 

Author Comment

by:palmer9
Comment Utility
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
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:palmer9
Comment Utility
Perfect!  Thank you!!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now