Hi,
Guys how to create view from two tables to display daily, monthly and yearly summary. I want to create view with following columns but can't work out the join query. Branchcode is the common in two tables. Please help.

select id, branchcode, monthyear, sum(total) as Received, sum(totalpaid) as Paid, sum(total-totalpaid) as Balance from deposit,expense where branchcode = 'x' and creationdate >= 'commondate' and invoicedate >= 'commondate' group by monthyear;

table deposit:
id
branchcode
creationdate
monthyear
total

select id, branchcode, creationdate, sum(total) from deposit where branchcode = 'x' and creationdate >= 'somedate' group by monthyear;

table expense:
id
branchcode
invoicedate
monthyear
totalpaid

select id, branchcode, invoicedate, sum(totalpaid) from expense where branchcode = 'x' and creationdate >= 'somedate' group by monthyear;

please repeat the exact columns that you wish to see in the result set

r937,

I need branchcode, monthyear, sum(deposit.total) as Received, sum(expense.totalpaid) as Paid, sum(deposit.total-expense.totalpaid) as Banance group by monthyear


select branchcode, monthyear, sum(total) as Received, sum(totalpaid) as Paid, sum(total-totalpaid) as Balance from deposit,expense where branchcode = 'x' and creationdate >= 'commondate' and invoicedate >= 'commondate' group by monthyear;

Thanks!

R937,

Thanks for the code. I tried to create a view with this query but it returns an error "View's SELECT contains a subquery in the FROM clause". Any workaround?

create a view for the UNION, then create a view for the outer query