1

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]
1
  • I take it there can be many rows per date in Expense, correct? Can there also be many rows per date in Budget? – Andriy M Jun 27 '16 at 12:03
1

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] ?

2
  • Check column names, [ExMount] [int] NULL, particulary, I used the names from table definitions in your post. – Serg Jun 27 '16 at 8:28
  • names are same dont know why it is saying that multi-part identifier could not found. (Invalid object name Budget ) how it can be possible ?? I have the same table name – user3809514 Jun 27 '16 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.