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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER