DB2 for IBM i (AS400) - SQL -

two tables (daily loan transaction table) , b (daily eod loan balance table).

trying write select statement in db2 as400 display daily loan transactions, eod loan balance , beginning loan balance (prior day) selected date range.

below query give me daily loan transactions , eod loan balance. need on modifying query below calculate beginning loan balance (balance of 02/28/2015) date range 03/01/2015 03/31/2015 selected.

select a.*, -- daily loan transactions        b.eod_loan_balance    inner join b     on a.date_id = b.date_id      , a.loan_num = b.loan_num a.date_id between 03/01/2015 03/31/2015 

note previous day should business day if 02/28/2015 not business day need pull eod balance of day before , should business day.

any idea appreciated.

thank you!

this 1 of many tasks easy "calender" or "dates" table...

select d.*,        b.eod_loan_balance,        e.eod_loan_balance, my_calendar_table c      join daily_trans d on c.date = d.date       join eod_bal b on c.date - 1 day= b.date , d.loan_num = b.loan_num      join eod_bal e on c.date = e.date , d.loan_num = e.loan_num c.date between 2015-03-01 , 2015-03-31   , c.is_business_day = 1; 

--edit--
looking @ this, realized doesn't quite work; join beginning balance won't give prior businsess day.

luckily, flexibility of calendar table comes rescue. include prior_business_day column in calendar table.

then sql becomes:

select d.*,        b.eod_loan_balance,        e.eod_loan_balance, my_calendar_table c      join daily_trans d on c.date = d.date      join eod_bal b on c.prior_buisiness_day = b.date             , d.loan_num = b.loan_num      join eod_bal e on c.date = e.date            , d.loan_num = e.loan_num c.date between 2015-03-01 , 2015-03-31   , c.is_business_day = 1;