Update row based on calculation of previous row - SQL Server

Question

Friday, April 13, 2018 7:14 PM

I want to update the whole table (shown in the picture) per id. As it shown in the picture I need to calculate value for columns in each row for each user_id :

interim balance = opening_balance + Debit+ Credit 

calculated_interest = (opening_balance + Debit+ Credit) * interest_rate

closing_balance = interim balance  + calculated_interest

The opening_balance for each row is the closing_balance from the previous row. Therefore, the opening_balance for the next row depends on calculation of the previous row.

I have tried to using lagging but since the calculation for previous row is not done, all values become 0.

Here is the code I have tried and it is not working.

UPDATE M
SET M.closing_balance = res.closing_balance + res.Credit + res.Debit
FROM [Test].[dbo].[Main] M
JOIN
    (SELECT
         a.[User_ID], a.[calender_day],  a.[Debit], a.[Credit], 
         LAG(closing_balance, 1, (a.[opening_balance] + a.[Debit] + a.[Credit])) OVER (PARTITION BY [User_ID] ORDER BY [calender_day] ASC) AS closing_balance
     FROM 
         [Test].[dbo].[Main] a) res ON res.[User_ID] = M.User_ID
                                    AND res.[calender_day] = M.[calender_day] 
User_ID calender_day Credit Debit opening_balance interim_balance calculated_interest
1 11/1/2017 0 0 100 0 0
1 11/2/2017 0 0 0 0 0
1 11/3/2017 0 0 0 0 0
1 11/4/2017 -3 0 0 0 0
1 11/5/2017 0 0 0 0 0
1 11/6/2017 0 0 0 0 0
1 11/7/2017 -4 0 0 0 0
1 11/8/2017 0 0 0 0 0
1 11/9/2017 0 0 0 0 0
1 11/10/2017 0 0 0 0 0
2 11/1/2017 0 0 200 0 0
2 11/2/2017 0 0 0 0 0
2 11/3/2017 -3 0 0 0 0
2 11/4/2017 0 4 0 0 0
2 11/5/2017 0 0 0 0 0
2 11/6/2017 0 0 0 0 0
2 11/7/2017 0 0 0 0 0
2 11/8/2017 0 0 0 0 0
2 11/9/2017 0 0 0 0 0
2 11/10/2017 0 0 0 0 0
3 11/1/2017 0 0 400 0 0
3 11/2/2017 -2 0 0 0 0
3 11/3/2017 0 0 0 0 0
3 11/4/2017 0 5 0 0 0
3 11/5/2017 0 0 0 0 0
3 11/6/2017 0 0 0 0 0
3 11/7/2017 -3 0 0 0 0
3 11/8/2017 0 0 0 0 0
3 11/9/2017 0 0 0 0 0

All replies (21)

Friday, April 13, 2018 7:40 PM

Similar ( not exact) issue and resolution in this thread 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc139cf6-44cb-4635-aa93-2e6a2b975459/tsql-populate-following-lines?forum=transactsql#65ed96b6-adf3-4af6-a7cf-ffb28dea4f43

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


Saturday, April 14, 2018 8:09 AM | 1 vote

Ideally the way to do it is to first get Opening balances loaded in the table for each group.

Then you can simply use running count calculation based on PARTITION BY and ORDER BY to get the running totals

Like

FIRST_VALUE(openingbalance) OVER (PARTITION BY UserID ORDER BY Calendar_day ) + SUM(Credit - Debit) OVER (PARTITION BY UserID ORDER BY Calendar_day )

This will ensure it start with opening balance set in first row for the group and keep adding the credit and substracting the debits up and until each row to get closing balances for each

to get opening balances for each day do like this

FIRST_VALUE(openingbalance) OVER (PARTITION BY UserID ORDER BY Calendar_day ) + SUM(Credit - Debit) OVER (PARTITION BY UserID ORDER BY Calendar_day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page


Sunday, April 15, 2018 7:06 AM | 1 vote

Can you post CREATE TABLE + INSERT INTO.... commands? And do not  forget the desired result

It looks like it very same a Running totals?

SUM(<col>) OVER(
ORDER BY <col>
ROWS BETWEEN 1 preceding and 1 following)

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence


Monday, April 16, 2018 1:47 PM

Hi

Thanks for your reply.

The sum over the result working, but there is problem in calculation. The exact calculation that should happen per day, per user id is this,

calculated_interest = ( ((Opening_balance +credit + debit)   * interest_rate) / 365)

Interim_balance =  credit + debit - calculated_interest

Closing_balance = Opening balance - Interim_balance

Therefore, closing_balance after this calculation would be opening balance for next day. I need to update each row and use the closing_balance for next row. Is there any way apart from loop to do this?

Thanks,

Elnaz


Monday, April 16, 2018 2:06 PM

Elanz

You can add PARTITION BY clause

SUM(<col>) OVER( PARTITION BY CAST(calender_day AS DATE) ,userid
ORDER BY <col>
ROWS BETWEEN 1 preceding and 1 following)

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence


Monday, April 16, 2018 2:29 PM

Hi,

Thanks for your reply.

The sum over the result working, but there is problem in calculation. The exact calculation that should happen per day, per user id is this,

Calculated_interest = ( ((Opening_balance +credit + debit)   * interest_rate) / 365)

Interim_balance =  credit + debit - Calculated_interest

Closing_balance = Opening balance - Interim_balance

Therefore, closing_balance after this calculation would be opening balance for next. I need to update each row and us the closing_balance for next row. Is there any way apart from loop to do this?

Thanks,

Elnaz

CREATE TABLE MasterTable (    User_ID bigint,    calender_day date,    Credit decimal(18, 8), Debit decimal(18, 8),   opening_balance decimal(18, 8), interim_balance decimal(18, 8), calculated_interest decimal(18, 8), closing_balance decimal(18, 8), interest_rate   decimal(5, 3));

I just loaded the data from Excel file that is attached in the original question. Please let me know if I provide anything else.

Thanks,

Elnaz


Monday, April 16, 2018 2:51 PM

Can you add PARTITION BY clause as I showed above?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence


Monday, April 16, 2018 5:45 PM

hey,

it is not working. when opening balance for current day is not updated the rest of values are 0. 


Monday, April 16, 2018 6:59 PM

Hi

Thanks for your reply.

The sum over the result working, but there is problem in calculation. The exact calculation that should happen per day, per user id is this,

calculated_interest = ( ((Opening_balance +credit + debit)   * interest_rate) / 365)

Interim_balance =  credit + debit - calculated_interest

Closing_balance = Opening balance - Interim_balance

Therefore, closing_balance after this calculation would be opening balance for next day. I need to update each row and use the closing_balance for next row. Is there any way apart from loop to do this?

Thanks,

Elnaz

Whats the issue here?

Isnt it just enough to extend the existing formula to add the additional parts? whats the issue in that?

like for example

((FIRST_VALUE(openingbalance) OVER (PARTITION BY UserID ORDER BY Calendar_day ) + SUM(Credit + Debit) OVER (PARTITION BY UserID ORDER BY Calendar_day )) * InterestRate ) /365

etc..

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page


Monday, April 16, 2018 9:23 PM

yes, I thought that is the solution, but in the part that we have (FIRST_VALUE(Openingbalance)), this opening balance is the closing balance from previous day. This opening balance should be updated otherwise it is 0.

If you notice the interest should be calculated based on what we have calculated on previous row.


Monday, April 16, 2018 9:29 PM

This thread is getting nowhere, it seems to me.

For this type of problem, you do yourself a big favour if you post CREATE TABLE statement for your tables and INSERT statements with sample data enough to illustrate all the angles of the problem. I see that you have already posted a CREATE TABLE statement, and there is a reference to an Excel book. But when we say INSERT statements, we mean INSERT statements. We need this so that we can develop a tested solution. Excel is not going to help us. (If we can construct the INSERT statements from Excel? Yeah, but that's boring. You are the one who have the problem, so you do the boring work.)

One thing I don't understand is this talk about the opening balance. If you don't have any ingoing balance for the first transaction, how is this ever going to work?


Tuesday, April 17, 2018 3:42 PM

Sorry, Here is the insert part for it 

CREATE TABLE MasterTable (
    ID bigint,
    calender_day date,
    Credit decimal(18, 8),
    Debit decimal(18, 8),
    opening_balance decimal(18, 8),
    interim_balance decimal(18, 8),
    calculated_interest decimal(18, 8),
    closing_balance decimal(18, 8),
    interest_rate   decimal(5, 3)
);

insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/1/2017',-5,2,100,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/2/2017',0,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/3/2017',0,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/4/2017',-3,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/5/2017',0,0,0,0,0,0, 3.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/6/2017',0,0,0,0,0,0, 3.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/7/2017',-4,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/8/2017',0,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/9/2017',0,0,0,0,0,0, 4.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/10/2017',0,0,0,0,0,0, 4.5)


insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/1/2017',0,0,200,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/2/2017',0,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/3/2017',-3,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/4/2017',0,4,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/5/2017',0,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/6/2017',0,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/7/2017',0,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/8/2017',0,0,0,0,0,0, 2)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/9/2017',0,0,0,0,0,0, 2)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/10/2017',0,0,0,0,0,0, 2)


insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/1/2017',0,0,400,0,0,0, 3.3)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/2/2017',-2,0,0,0,0,0, 2.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/3/2017',0,0,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/4/2017',0,5,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/5/2017',0,0,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/6/2017',-3,0,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/7/2017',0,0,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/8/2017',-2,0,0,0,0,0, 3.1)

the problem is with some values that is calculated in the middle,

calculated_interest = ( ((Opening_balance +credit + debit)   * interest_rate) / 365)

Interim_balance =  credit + debit + calculated_interest

Closing_balance = Opening balance - Interim_balance

The Closing balance would be updated and then we use this closing balance as an opening balance for next day. 

for example for data in second row the value is : 
calculated_interest  = ((100 -5 + 2 ) * 2.5) = 0.66

Interim_balance  = -5+3 + 0.66 = - 2.66

closing balance = 100 -2.66 = 97.34

There problem is the new balance is increasing since the interest rate is applied daily. it is not simply adding the credit and debit from previous days. 

Is there any way to do this daily calculation. 


Tuesday, April 17, 2018 9:34 PM

Thanks for the test data. This requires some more thinking.

In the meanwhile, can you check you test data? ID 1 reoccurs with the same dates, that is intended to be a different ID, isn't it?

Also, I'm a bit perplex of the interest being capitalised daily. I'm inclined to call that usury. :-) Are you sure that these are the correct business requirements? Or this is a mockup of the actual case?

It would also help if you can compute the expected values, as this makes testing easier.


Wednesday, April 18, 2018 7:29 PM

Hi

Sorry, for delay. I have edited my previous comment and the data is now correct. yes, this is loan forward calculation that calculates balance per day and uses that as start point for next day calculation. This is mock up from actual data. The main problem is to calculate the interest based on opening balance and keep adding that per day. 

I created the desired output and attached the excel file. 

Thanks again for help :)

  ID calender_day Credit Debit opening_balance  interim_balance calculated_interest closing_balance interest_rate
1 11/1/2017 -5 2 100 97 0.664383562 97.66438356 2.5
1 11/2/2017 0 0 97.6643836 97.66438356 0.668934134 98.3333177 2.5
1 11/3/2017 0 0 98.3333177 98.3333177 0.673515875 99.00683357 2.5
1 11/4/2017 -3 0 99.0068336 96.00683357 0.657581052 96.66441462 2.5
1 11/5/2017 0 0 96.6644146 96.66441462 0.926919044 97.59133367 3.5
1 11/6/2017 0 0 97.5913337 97.59133367 0.935807309 98.52714098 3.5
1 11/7/2017 -4 0 98.527141 94.52714098 0.647446171 95.17458715 2.5
1 11/8/2017 0 0 95.1745871 95.17458715 0.651880734 95.82646788 2.5
1 11/9/2017 0 0 95.8264679 95.82646788 1.181422207 97.00789009 4.5
1 11/10/2017 0 0 97.0078901 97.00789009 1.195987686 98.20387777 4.5
2 11/1/2017 0 0 200 200 0.602739726 200.6027397 1.1
2 11/2/2017 0 0 200.60274 200.6027397 0.604556202 201.2072959 1.1
2 11/3/2017 -3 0 201.207296 198.2072959 0.597337056 198.804633 1.1
2 11/4/2017 0 4 198.804633 202.804633 0.611192045 203.415825 1.1
2 11/5/2017 0 0 203.415825 203.415825 0.613033993 204.028859 1.1
2 11/6/2017 0 0 204.028859 204.028859 0.614881493 204.6437405 1.1
2 11/7/2017 0 0 204.643741 204.6437405 0.61673456 205.2604751 1.1
2 11/8/2017 0 0 205.260475 205.2604751 1.124714932 206.38519 2
2 11/9/2017 0 0 206.38519 206.38519 1.130877753 207.5160678 2
2 11/10/2017 0 0 207.516068 207.5160678 1.137074344 208.6531421 2
3 11/1/2017 0 0 400 400 3.616438356 403.6164384 3.3
3 11/2/2017 -2 0 403.616438 401.6164384 2.310669919 403.9271083 2.1
3 11/3/2017 0 0 403.927108 403.9271083 3.430613796 407.3577221 3.1
3 11/4/2017 0 5 407.357722 412.3577221 3.50221627 415.8599383 3.1
3 11/5/2017 0 0 415.859938 415.8599383 3.53196112 419.3918995 3.1
3 11/6/2017 0 0 419.391899 419.3918995 3.561958598 422.9538581 3.1
3 11/7/2017 -3 0 422.953858 419.9538581 3.566731397 423.5205895 3.1
3 11/8/2017 0 0 423.520589 423.5205895 3.597024184 427.1176136 3.1
3 11/9/2017 0 0 427.117614 427.1176136 3.627574253 430.7451879 3.1
3 11/10/2017 -2 0 430.745188 428.7451879 3.641397486 432.3865854 3.1

Wednesday, April 18, 2018 8:21 PM | 1 vote

It seems that there is not a set-based solution for this problem. I have one below, but you will find that it does not produce the correct result, even if the deviations are fairly small. I also have a loop solution. I simply had to write it to be able to verify my set-based solution. So you get that one for free.

I still like to dwell on the set-based solution. It first computes the balance per day as a running sum without considering the interest. It then computes the interest as a running product. Now, there is no PROD aggregate in SQL Server, but you can emulate it by making use of that exp(log(a) + log(b) = a*b. I like to point out that this is a perfectly valid way of computing accumulated capitalised interest, as long as the underlying amount does not change. I added some extra test data, and the two solutions produce the same result for ID = 20 which has no debits and credits. But when the debits and credits come into play, the scheme breaks down.

As for the loop solution, you should pay attention to the fact that I loop by date only. That is, I'm handling all IDs in parallel. This makes the loop more efficient.

You will find that both solutions produces different result from what you indiciated above. This is because I believe that you have overlooked that the interest rate should be divided by 100 as it is a percentage.

Here is the complete script with your and my test data.

CREATE TABLE MasterTable (
    ID bigint,
    calender_day date,
    Credit decimal(18, 8),
   Debit decimal(18, 8),
   opening_balance decimal(18, 8),
   interim_balance decimal(18, 8),
   calculated_interest decimal(18, 8),
   closing_balance decimal(18, 8),
   interest_rate    decimal(5, 3)
);

insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/1/2017',-5,2,100,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/2/2017',0,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/3/2017',0,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/4/2017',-3,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/5/2017',0,0,0,0,0,0, 3.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/6/2017',0,0,0,0,0,0, 3.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/7/2017',-4,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/8/2017',0,0,0,0,0,0, 2.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/9/2017',0,0,0,0,0,0, 4.5)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(1,'11/10/2017',0,0,0,0,0,0, 4.5)


insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/1/2017',0,0,200,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/2/2017',0,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/3/2017',-3,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/4/2017',0,4,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/5/2017',0,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/6/2017',0,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/7/2017',0,0,0,0,0,0, 1.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/8/2017',0,0,0,0,0,0, 2)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/9/2017',0,0,0,0,0,0, 2)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(2,'11/10/2017',0,0,0,0,0,0, 2)


insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/1/2017',0,0,400,0,0,0, 3.3)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/2/2017',-2,0,0,0,0,0, 2.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/3/2017',0,0,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/4/2017',0,5,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/5/2017',0,0,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/6/2017',-3,0,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/7/2017',0,0,0,0,0,0, 3.1)
insert into MasterTable(ID,calender_day,Credit,Debit,opening_balance,interim_balance,calculated_interest, closing_balance,interest_rate) values(3,'11/8/2017',-2,0,0,0,0,0, 3.1)

INSERT MasterTable (ID, calender_day, Credit, Debit, opening_balance, interest_rate)
   VALUES(20, '20171101', 0, 0, 1000, 3.14),
         (20, '20171102', 0, 0, 0, 3.56),
         (20, '20171103', 0, 0, 0, 2.24),
         (20, '20171104', 0, 0, 0, 3.16),
         (20, '20171105', 0, 0, 0, 4.04),
         (20, '20171106', 0, 0, 0, 3.06),
         (20, '20171107', 0, 0, 0, 3.34),
         (20, '20171108', 0, 0, 0, 1.56),
         (20, '20171109', 0, 0, 0, 2.62),
         (20, '20171110', 0, 0, 0, 3.12),
         (20, '20171111', 0, 0, 0, 2.87),
         (21, '20171101', 0, 0, 1000, 3.56),
         (21, '20171102', -900, 0, 0, 3.56),
         (21, '20171103', 0, 0, 0, 2.24),
         (21, '20171104', 0, 0, 0, 3.16),
         (21, '20171105', 0, 900, 0, 4.04),
         (21, '20171106', 0, 0, 0, 3.06),
         (21, '20171107', 0, 0, 0, 3.34),
         (21, '20171108', 0, 0, 0, 1.56),
         (21, '20171109', 0, 0, 0, 2.62),
         (21, '20171110', 0, 0, 0, 3.12),
         (21, '20171111', 0, 0, 0, 2.87)


go
SELECT * FROM MasterTable
go
; WITH balance AS (
   SELECT ID, calender_day, Credit, Debit, opening_balance, interest_rate,
          SUM((opening_balance + Credit + Debit )) 
              OVER (PARTITION BY ID
                    ORDER BY calender_day 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS outgoing_balance
   FROM MasterTable
) 
SELECT ID, calender_day, Credit, Debit, opening_balance, outgoing_balance, 
       EXP (SUM (CASE WHEN outgoing_balance > 0
                      THEN LOG(1 + interest_rate / 100 / 365)
                      ELSE 0
                 END)
                 OVER (PARTITION BY ID
                      ORDER BY calender_day 
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           ) * outgoing_balance AS closing_balance
FROM   balance
ORDER BY ID, calender_day
go  
DECLARE @date date,
        @cur CURSOR

SET @cur = CURSOR STATIC FOR
   SELECT DISTINCT calender_day 
   FROM   MasterTable
   ORDER  BY calender_day

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @date
   IF @@fetch_status <> 0
      BREAK

   UPDATE today
   SET    opening_balance     = coalesce(yester.closing_balance, today.opening_balance),
          calculated_interest = (coalesce(yester.closing_balance, today.opening_balance) +
                                 today.Credit + today.Debit) * today.interest_rate / 100 / 365,
          interim_balance     = today.Credit + today.Debit + 
                                (coalesce(yester.closing_balance, today.opening_balance) +
                                 today.Credit + today.Debit) * today.interest_rate / 100 / 365,
          closing_balance     = coalesce(yester.closing_balance, today.opening_balance) + 
                                (today.Credit + today.Debit + 
                                 (coalesce(yester.closing_balance, today.opening_balance) +
                                  today.Credit + today.Debit) * today.interest_rate / 100 / 365)
   FROM  MasterTable today
   LEFT  JOIN MasterTable yester ON yester.ID = today.ID
                                AND yester.calender_day = dateadd(DAY, -1, @date)
   WHERE today.calender_day = @date
END

SELECT * FROM MasterTable ORDER BY ID, calender_day
go
DROP TABLE MasterTable

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


Thursday, April 19, 2018 6:40 PM

Thanks a lot for help and the code :). The while solution works well, I just let it run for my DB with 143 million records, I think that would take long time. 


Thursday, April 19, 2018 7:44 PM

For the loop, you absolutely need an index on (calendar_day, ID) with the columns in the right order.

I checked the solution suggested by Peter (SwePeso), and his solution produces a better approximation than my set-based solution, but there are still deviations from my loop, which I believe to be the truth (as long as you really have daily capitalisations, which I don't really believe in!).

With your test data the differences were very small and ignorable. But I added a test case where the opening balance was 10 million and where there were daily credits between -1000 and -7000 depending on the day of the week and I let this run for a year. The correct outgoing balance was 8769409, but Peter's solution said 8766650, which means that someone loses. (My own set-based solution was even worse, 8751673.)

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


Thursday, April 19, 2018 8:31 PM

Thanks, ya I already indexed the data as you said based on ID and day. you are right since the values are millions in my real dataset I need exact calculation and approximation does not work. 


Thursday, April 19, 2018 8:32 PM

Thanks a lot for reply. The problem is this approach gives me approximate values, that has big effect on million dollar opening balance.


Friday, April 20, 2018 6:52 AM

I like to add that I can't escape that Peter's solution is the one that is giving you the correct values after all. To wit, I am very skeptic to that you have the business requirements correct. Adding the daily interest to the outgoing balance, that is daily capitalisation, is a very odd thing to do. On a savings account, the interest is in many cases only capitalised yearly, although I've also seen monthly and quarterly. For loans, the interest is not typically not capitalised, but you have to pay it up front.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


Friday, April 20, 2018 8:31 AM

Here's another version

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp



SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY calender_day) AS Seq INTO #Temp
FROM MasterTable


CREATE CLUSTERED INDEX IDX_Clust ON #Temp(ID,calender_day)


DECLARE @ID int,@Seq int,@Calender_day datetime, @OpenBal Decimal(30,8),@CalcInt Decimal(30,8),@ClosingBal Decimal(30,8)


SELECT TOP 1 @ID = ID,@Calender_day = Calender_day,@Seq = Seq, @OpenBal = Opening_balance,@ClosingBal = closing_balance,@CalcInt= calculated_interest
FROM #Temp
ORDER BY ID,calender_day

--SELECT  @ID ,@Calender_day ,@Seq , @OpenBal ,@ClosingBal,@CalcInt

UPDATE t
SET @OpenBal = Opening_balance = CASE WHEN ID = @ID AND Seq = @Seq THEN CAST(@OpenBal AS Decimal(30,8)) WHEN ID = @ID AND Seq = @Seq + 1 THEN CAST(@ClosingBal AS Decimal(30,8)) ELSE CAST(opening_balance AS Decimal(30,8)) END,
 @CalcInt = calculated_interest = CASE WHEN ID = @ID AND Seq = @Seq  THEN  CAST(( opening_balance + Credit + Debit) * (interest_rate /365.00000000) AS Decimal(30,8))
 WHEN ID = @ID AND Seq = @Seq + 1 THEN  CAST( ( @ClosingBal + Credit + Debit) * (interest_rate /365.00000000 )AS Decimal(30,8))
ELSE CAST(( opening_balance + Credit + Debit) * (interest_rate /365.00000000) AS Decimal(30,8)) END,
@ClosingBal = closing_balance = CASE WHEN ID = @ID AND Seq = @Seq  THEN  CAST(( opening_balance + Credit + Debit) + (( opening_balance + Credit + Debit) * interest_rate /365.00000000) AS Decimal(30,8))
WHEN ID = @ID AND Seq = @Seq + 1 THEN CAST(( @ClosingBal + Credit + Debit) + (( @ClosingBal + Credit + Debit) * interest_rate /365.00000000)  AS Decimal(30,8))
ELSE CAST(( opening_balance + Credit + Debit) + (( opening_balance + Credit + Debit) * interest_rate /365.00000000) AS Decimal(30,8)) END,
@ID = ID,@Calender_day = Calender_day,@Seq = Seq
FROM #Temp t WITH (TABLOCKX)
OPTION (MAXDOP 1)

UPDATE m
SET interim_Balance = t.opening_balance + m.Credit + m.Debit,
opening_balance = t.opening_balance,
calculated_interest = t.calculated_interest,
closing_balance = t.closing_balance
FROM MasterTable m
JOIN #Temp t
ON t.ID = m.ID
AND t.Calender_Day = m.Calender_Day

SELECT * FROM MasterTable

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page