SQL daily sum report

Question

I have two tables, one is income which consists of ID, income_amount and date the other is expenses which is ID, amount_spent and date.

I'm trying to display a table with three columns the daily total of income, the daily total of amount and the date for that day, it is possible for that day to have no income or amount but not necessarily both.

I was able to display the table in visual c# by gathering them in individual tables and deriving the results programmatically but is there a way to achieve that table with just a single sql query?

Answer 1

trunc_to_day here is an hypothetical function which truncates a date to its day (you didn't specify what RDMBS you are using):

select sum(incomes), sun(spent), day from (
   (select income_amount incomes, 0 spent, trunc_to_day(datecol) day from income_table)
   union all
   (select 0 incomes, amount_spent spent, trunc_to_day(datecol) day from spent_table)
) group by day;

Finally, if you want to limit to some days, use a where statement on it.