?
Solved

Help with calculation

Posted on 2009-04-07
5
Medium Priority
?
152 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 93

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 2000 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

762 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