Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • Last Modified:

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

0
palmer9
Asked:
palmer9
  • 2
  • 2
1 Solution
 
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
 
palmer9Author Commented:
Perfect!  Thank you!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now