asked on

Subtracting columns in different tables

We are building a benefit time tracking app. I simply want to sum a column in the TimeUsed table and subtract it from the sum of a column in the TimeEarned table based on StaffMemeberID.

The following querie works when executed in SQL Management Studio (2008), but when it is pasted into Visual Studio 2012's Data Source wizard it only returns the results from the first select statement:

Select (Select SUM(VacationEarned)
from TimeEarned
Where StaffMemberID=3)
- (Select SUM(VacationUsed)
from TimeUsed where StaffMemberID=3)As [Vacation Left]

Select(Select SUM(PersonalEarned)
from TimeEarned
Where StaffMemberID=3)
- (Select SUM(PersonalUsed)
from TimeUsed where StaffMemberID=3)As [Personal Left]

Select(Select SUM(SickEarned)
from TimeEarned
Where StaffMemberID=3)
- (Select SUM(SickUsed)
from TimeUsed where StaffMemberID=3)As [Sick Left]

Any help will be appreciated

ICantSee

ASKER

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

This is all you need:

SELECT
SUM(VacationEarned) - SUM(VacationUsed) AS [Vacation Left],
SUM(PersonalEarned) - SUM(PersonalUsed) AS [Personal Left],
SUM(SickEarned) - SUM(SickUsed) AS [SickLeft]
FROM TimeEarned
WHERE
StaffMemberID=3

ICantSee

ASKER

ScottPletcher

thank you for your answer. It doesn't work because VacationUsed, SickUsed, PersonalUsed does not come from TimeEarned. The code throws the error accordingly.

ICantSee

ASKER

Awesome. THANK YOU!!!!