DB2 for IBM i (AS400) - SQL

Question

Two tables A (daily Loan Transaction table) and B (daily eod Loan Balance table).

Trying to write a select statement in DB2 AS400 to display daily loan transactions, eod Loan Balance and beginning loan balance (prior day) for selected date range.

Below query would give me daily loan transactions and eod loan balance. But need some help on modifying the query below to calculate beginning loan balance as well (balance as of 02/28/2015) for date range 03/01/2015 to 03/31/2015 selected.

select A.*, -- daily loan transactions
       B.EOD_Loan_Balance
from A 
  inner join B 
   on A.date_id = B.date_id 
    and A.Loan_num = B.Loan_num
where a.date_id between 03/01/2015 to 03/31/2015

Note that previous day should be business day so if 02/28/2015 is not a business day then we need to pull eod balance of day before that and which should be a business day.

Any idea would be appreciated.

Thank you!

Answer 1

Would this work for you?

select A.*, -- daily loan transactions
       B.EOD_Loan_Balance,
       C.EOD_Loan_Balance,
from A 
  inner join B 
   on A.date_id = B.date_id 
    and A.Loan_num = B.Loan_num
  LEFT OUTER join C
   on A.Loan_num = C.Loan_num
where a.date_id between '2015-03-01' to '2015-03-31'
and c.date_id = CASE 
                  WHEN DAYOFWEEK('2015-03-01') = 1 THEN '2015-03-01' - 2 days 'Sunday --> Friday
                  WHEN DAYOFWEEK('2015-03-01') = 2 THEN '2015-03-01' - 3 days 'Monday --> Friday
                  ELSE                                  '2015-03-01' - 1 day ' Previous day
                END

Untested as I don't have a DB2 instance handy.

EDIT Revised to allow for weekends as mentioned in comments.

Answer 2

This is one of the many tasks that are easy to do with a "calender" or "dates" table...

select D.*,
       B.EOD_Loan_Balance,
       E.EOD_Loan_Balance,
from MY_CALENDAR_TABLE C
     join daily_trans D on c.date = d.date 
     join eod_bal B on c.date - 1 day= b.date and d.loan_num = b.loan_num
     join eod_bal E on c.date = e.date and d.loan_num = e.loan_num
where c.date between 2015-03-01 and 2015-03-31
  and c.is_business_day = 1;

--Edit--
Looking back at this, I realized that it doesn't quite work; the join from beginning balance won't give you the prior businsess day.

Luckily, the flexibility of a calendar table comes to the rescue. Just include a PRIOR_BUSINESS_DAY column in your calendar table.

Then the SQL becomes:

select D.*,
       B.EOD_Loan_Balance,
       E.EOD_Loan_Balance,
from MY_CALENDAR_TABLE C
     join daily_trans D on c.date = d.date
     join eod_bal B on c.prior_buisiness_day = b.date 
           and d.loan_num = b.loan_num
     join eod_bal E on c.date = e.date
           and d.loan_num = e.loan_num
where c.date between 2015-03-01 and 2015-03-31
  and c.is_business_day = 1;