Member Avatar for Ombu

I want to be able to write an SQL statement that pulls the sum of revenue and expenditure for each day and gets net income for each day with the distinct dates available in the Income and Expenditure tables. I have tried the following statement, but I'm not getting the desired result. I'm using MS Access.

SELECT
    Income.IncomeDate,
    SUM(Income.AmountPaid) AS IncomeAmount,
    Expenditure.ExpenseDate,
    SUM(Expenditure.TotalAmount) AS ExpenseAmount,
    IncomeAmount - ExpenseAmount AS NetIncome
FROM Income FULL OUTER JOIN Expenditure ON Income.IncomeDate=Expenditure.ExpenseDate
GROUP BY Income.IncomeDate
ORDER BY Income.IncomeDate
  • 2 Contributors
  • 1 Reply
  • 32 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

Can you create an SQLFiddle, so we can see (part of) the data you have? Then post your desired result, so people can see what you would like to have.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.