CODEWITHSUNDEEP
sqloracle-databaseoracle10g
user3064554
user3064554

Reputation: 31

Running total for interest calculation

I want to calculate the interest for financial balances for the whole financial year. I am able to get a running balance for a particular date when there is an entry in the ledger table, but when there is no entry my interest calculation goes haywire. Below is the ledger table: 

OOWNCODE     DTOFTRAN     VOUCHER          DRCR     DAMOUNT     CAMOUNT
00100VS055     04/01/2013   OPNG001NSE0      Debit     1303.87    0.00  
00100VS055     05/15/2013   JVOFSET0000025   Debit     337.08     0.00

Below is the running ledger balance query: 

Select ledger.oowncode as "Clientcode",
   to_char(ledger.dtoftran, 'DD/MM/YYYY') as "Date",
   sum(ledger.damount - ledger.camount) as "Ledger",
   Sum(Sum(Ledger.Damount - Ledger.Camount)) over(Partition by Ledger.Oowncode Order by                Ledger.Oowncode,ledger.dtoftran rows between unbounded preceding and current row) as "Running Balance",
   round((Sum(Sum(Ledger.Damount - Ledger.Camount)) over(Partition by Ledger.Oowncode Order by Ledger.Oowncode,ledger.dtoftran rows between unbounded preceding and current row)*18/365),2) as "Interest"
   from ledger ledger
where ledger.firmnumber = 'SUG-000001'
   and ledger.oowncode = '00100VS055'
   and ledger.dtoftran >='01-APR-2013'
   and ledger.dtoftran <='31-MAY-2013'
group by ledger.oowncode, ledger.dtoftran

And below is the result: 

Clientcode          Date          Ledger          Running           BalanceInterest
00100VS055          01/04/2013      1303.87        1303.87        64.3  
00100VS055          15/05/2013       337.08        1640.95            80.92

Now logically, running a balance of 1303.87 is running balance for every day till 15/05/2013, and then 1640.95 is the running balance for the next days. I want the result to be like this: 

Clientcode  Date    Ledger  Running Balance Interest  
00100VS055  01/04/2013  1303.87 1303.87 64.3  
00100VS055  02/04/2013  1303.87 1303.87 64.3  
00100VS055  03/04/2013  1303.87 1303.87 64.3  
00100VS055  04/04/2013  1303.87 1303.87 64.3

and so on up untill 15 may 2013. 

.   
00100VS055  15/05/2013  337.08  1640.95 80.92

Could you please help me on this ground?

Hi David,
As per your help I have done the necessary coding: 

WITH l AS
(SELECT oowncode,dtoftran,damount,camount from ledger
WHERE firmnumber='DDA-000001' and oowncode='ACCOPEN'
GROUP BY oowncode,dtoftran,damount,camount)
Select l.oowncode,cte_dates.dtoftran,sum(l.damount-l.camount) running
From   (SELECT dtoftran
FROM ledger
WHERE dtoftran BETWEEN
TO_DATE('01/04/2011', 'DD/MM/YY')
AND TO_DATE('31/03/2012', 'DD/MM/YY') group by dtoftran) cte_dates 
left outer join l  
partition by (oowncode) on (cte_dates.dtoftran = l.dtoftran) 
group by l.oowncode,cte_dates.dtoftran

And below is the output: 

1   ACCOPEN     01/04/2011  12350
2   ACCOPEN     02/04/2011  
3   ACCOPEN     04/04/2011  
4   ACCOPEN     05/04/2011  
5   ACCOPEN     06/04/2011  
6   ACCOPEN     07/04/2011  2600
7   ACCOPEN     08/04/2011  -650
8   ACCOPEN     09/04/2011  
9   ACCOPEN     11/04/2011  
10  ACCOPEN     12/04/2011  
11  ACCOPEN     13/04/2011  650
12  ACCOPEN     14/04/2011  
13  ACCOPEN     15/04/2011  9100
14  ACCOPEN     16/04/2011

I even want on 02/04/2011 it should bring in the running total of windowing clause. Could you guide me where am I wrong?

Upvotes: 1

Views: 591

Answers (1)

David Aldridge
David Aldridge

Reputation: 52356

This is a data densification problem, in which you need to ensure that there is a record for every possible value -- in your case, for every value of Ledger.Oowncode and ledger.dtoftran.

Oracle 10g has a partition join syntax for exactly this purpose: http://docs.oracle.com/cd/B19306_01/server.102/b14223/analysis.htm#i1014934

In your case you use a query to generate a list of dates, which you partition outer join to the ledger table. Partition by the oowncode and join on the date. That will densify the data, and the you apply the window functions to perform the calculation on top of that.

  1. Write a query that returns just the code, the date, and the total transaction amount, and place it in a common table expression (WITH clause) named cte_transactions.
  2. Write a query that returns a list of dates in a CTE, named cte_dates
  3. Join them in another cte using the join syntax: from cte_dates d left outer join cte_transactions t partition by (oowncode) on (d.transaction_date = t.transaction_date
  4. Apply the window clause to calculate the running total.

Upvotes: 2

Related Questions