Link to home
Start Free TrialLog in
Avatar of hariaspind
hariaspind

asked on

How to write a sql Query to subtract amount by Category ,data are in 2 tables?

I have 2 query’s as below, each return 9 category and year1amount and year2amount.Now i need to subtract table A from table B data, category wise and get a result set .Which shows
9 category and year1amount and year2amount correspondingly.



Select 'TableA'
select
      MB.CategoryID,
      Sum(MB.Year1Amount) as Year1Amount,
      Sum(MB.Year2Amount) as Year2Amount
from
      MaintenanceBudget MB
INNER JOIN [Plan] P
On
      MB.Planid=P.ID
Where
      P.PlanVersionId=2
Group by
      MB.CategoryID

Select 'Table B'
select
MPB.CategoryID,
Sum(MPB.Year1Amount) as Year1Amount,
Sum(MPB.Year2Amount) as Year2Amount
from
MaintenanceProjectBudget MPB
Inner join
      MaintenanceProject MP
On
      MPB.ProjectID=MP.ID
Inner join [Plan] P
On
      MP.Planid=P.ID
Where P.PlanVersionId=2
Group by
MPB.CategoryID
Order by MPB.CategoryID
 User generated image
ASKER CERTIFIED SOLUTION
Avatar of AliSyed
AliSyed
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hariaspind
hariaspind

ASKER

I have done without CTE ,it works.Thanks .