Solved

Help with calculation

Posted on 2009-04-07
5
146 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
ID: 24090192
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
ID: 24090193
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
ID: 24090267
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
ID: 24090378
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
ID: 31567637
Perfect!  Thank you!!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

919 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

15 Experts available now in Live!

Get 1:1 Help Now