Sum data from two tables

[robertw]

Hi Guys,

I have a predicament here which I think is really easy if you get it right.
I just cant wrap my brain around it for the moment (probably been staring at
the screen too long)

I have two table (financial data)

Table 1 looks as follows

id account fiscalyear openingbalance
1 1202 2011 1122.00
2 1210 2011 0.0
3 1920 2011 1212.33

Table 2 handled transactions and looks like this

id FK_account date amount
1 1210 2010-01-01 23.00
2 1210 2010-01-23 566
3 1920 2010-01-21 456
4 1202 2010-01-14 956

What I want to do is to create a four column balance statement here listed
per account with two opening variables, StartDate and EndDate

The columns are
1. Opening balance, Fiscalyear
in this case ob from table 1
2. Opening balance, StartDate
which is ob plus the sum of transactions up until StartDate
3. Closing balance, EndDate
which is ob plus the sum of transactions up until EndDate
4. Difference, period
which is column 4 minus column 3

Problem is that I cannot find an SQL query that can collect every account and it opening balance from table 1 -> sum it with transactions for that particular account and period from table 2 -> and present it in the same query.

Please, please help me here. (My lack of intelligence is starting to drive me crazy over here...)

[smantscheff]

drop table if exists accounts;
create table accounts 
(id integer, account integer, fiscalyear integer, openingbalance float );
insert into accounts values 
('1','1202','2011','1122.00'),
('2','1210','2011','0.0'),
('3','1920','2011','1212.33');

drop table if exists transactions;
create table transactions (id integer, account integer, date date, amount float);
insert into transactions values 
('1','1210','2010-01-01','23.00'),
('2','1210','2010-01-23','566'),
('3','1920','2010-01-21','456'),
('4','1202','2010-01-14','956');

select a.account, fiscalyear, openingbalance, openingbalance + sum(amount) as closingbalance, 
sum(amount) as difference
from accounts a, transactions t
where a.account=t.account and year(date)+1 = fiscalyear
group by a.account

+---------+------------+----------------+------------------+------------+
| account | fiscalyear | openingbalance | closingbalance   | difference |
+---------+------------+----------------+------------------+------------+
|    1202 |       2011 |           1122 |             2078 |        956 |
|    1210 |       2011 |              0 |              589 |        589 |
|    1920 |       2011 |        1212.33 | 1668.32995605469 |        456 |
+---------+------------+----------------+------------------+------------+

[robertw]

Thanks a lot smantscheff,

This works perfectly!!

And your quick response is most appreciated.

[smantscheff]

Please mark this thread as solved.