Hi all,

For a Banking application, I have below doubt for how to write SQL query to get desired output.
Let us first understand the scenario.

There is table A with 4 columns. Account_NO, Date_From, Date_To, Interest_Amount.

Now during a month when ever there is a change in account balance due to any transaction, row is maintained in this table.
For example, Account 1 has credited 1000 USD on 1st of March'15.

Row will be maintained like below for above four fields respectively.

1000 | 01/03/2015 | NULL | NULL

Now on 5th March'15 Customer has done another transaction say amount 500 USD debit from the same account.
Then above row will be updated like this and a new row will be inserted.

1000 | 01/03/2015 | 05/03/2015 | 0.23
500 | 05/03/2015 | NULL | NULL

Basically it calculates interest when ever there is a change in balance and maintained the Interest Amount.

Now, during a year for each month many rows can be maintained. Like below.

1000 | 01/03/2015 | 05/03/2015 | 0.23
500 | 05/03/2015 | 31/03/2015 | 2.56 --Month end so cutoff
500 | 01/04/2015 | NULL | NULL
500 | 01/04/2015 | 07/04/2015 | 0.18 --200 Credit on 8th April'15
700 | 08/04/2015 | NULL | NULL
700 | 08/04/2015 | 30/04/2015 | 1.53

And so on for other months as per transaction date and amount , interest is being calculated.

Now at the end of year, I want to figure it out per month interest calculated for a customer.

So how can I write a SQL on above format of data to get desired output.

Any help will be appreciated. Thanks in advance.

SQL> create table T1
  2   ( acct_no number,
  3     d_from  date,
  4     d_to    date,
  5     interest number );

Table created.

SQL>
SQL> insert into T1 values (1000, date '2016-01-03',date '2016-01-07', 12 );

1 row created.

SQL> insert into T1 values (1000, date '2016-01-07',date '2016-01-13', 7 );

1 row created.

SQL> insert into T1 values (1000, date '2016-01-13',date '2016-01-22', 9 );

1 row created.

SQL> insert into T1 values (1000, date '2016-01-22',date '2016-01-31', 21 );

1 row created.

SQL>
SQL> insert into T1 values (1000, date '2016-02-22',date '2016-02-25', 21 );

1 row created.

SQL> insert into T1 values (1000, date '2016-02-25',null,null );

1 row created.

SQL>
SQL> insert into T1 values (2000, date '2016-01-03',null, null );

1 row created.

SQL>
SQL> insert into T1 values (3000, date '2016-01-03',date '2016-01-07', 12 );

1 row created.

SQL> insert into T1 values (3000, date '2016-01-07',null,null );

1 row created.

SQL>
SQL> select acct_no, trunc(d_from,'MM') mth, nvl(sum(interest),0) mth_interest
  2  from t1
  3  group by acct_no, trunc(d_from,'MM')
  4  order by 1,2;

   ACCT_NO MTH       MTH_INTEREST
---------- --------- ------------
      1000 01-JAN-16           49
      1000 01-FEB-16           21
      2000 01-JAN-16            0
      3000 01-JAN-16           12

SQL>