Sql-server – Subtract TotalExpense from BudgetAmount In Budget Table for a specific date

sql server

I have a Budget table which has the following columns:

BudgetId, BudgetDate, BudgetAmount

and a table Expense which has the following columns:

ExpenseID, Date, ExDescrip, Category_fk, ExAMount

How can I subtract daily expense from the budget of a sepecific date?

I am using this query to get the sum of the expenses. This query is ok to give the total expense of that date from budget.

select sum(ExMount) as totalExpense
from Budget b, Expense ex
where b.BudgetDate=ex.Date

Now, how can I subtract totalExpense from the BudgetAmount from the budget table?

CREATE TABLE [dbo].[Budget]
( [BudgetId] [int] IDENTITY(1,1) NOT NULL, 
  [BudgetDate] [date] NULL, 
  [BudgetAmount] [int] NULL, 
  CONSTRAINT [PK_Budget] 
    PRIMARY KEY CLUSTERED ( [BudgetId] ASC )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] 
GO

The script of Expense table is 

CREATE TABLE [dbo].[Expense](
    [ExpenseID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NULL,
    [ExDescrip] [varchar](50) NULL,
    [Category_fk] [int] NOT NULL,
    [ExMount] [int] NULL,
    [BudgetFk] [int] NULL,
 CONSTRAINT [PK_Expense] PRIMARY KEY CLUSTERED 
(
    [ExpenseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Best Answer

To start with, JOIN and GROUP BY

select
    b.BudgetDate,
    sum(b.BudgetAmount) - coalesce(sum(ex.ExMount),0) as BudgetLessExpenses
from dbo.Budget b
left join dbo.Expense ex
on b.BudgetDate=ex.Date
group by b.BudgetDate

BTW. Shouldn't there be a UNIQUE constraint on [Budget].[BudgetDate] ?

Related Question