Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive RunningTotal calculate


Recursive RunningTotal calculate

Author
Message
Catcha
Catcha
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 169
Hi All,

I need to calculate running total (totalInterestedPaid) for the following temp table (#tmpInterestedPaid)

tran_date Calc_interest_accrued Interest_paid TotalInterestPaid
20120908 54.06 NULL 0
20120910 54.06 NULL 0
20120911 24.04 112.35 0
20120913 23.67 20.12 0
20120914 23.67 NULL 0

The totalInterestedPaid should be additional the previous date Calc_interest_accrued with today Calc_interest_accrued but whenever there is interest_paid is not equal to null the Running total will be reset to zero.

The expected result is as follow

tran_date Calc_interest_accrued Interest_paid TotalInterestPaid
20120908 54.06 NULL 54.06
20120910 54.06 NULL 108.12
20120911 24.04 112.35 132.16
20120913 23.67 20.12 23.67
20120914 23.67 NULL 47.34

I believe recursive CTE will work but just not sure how to code it.

Thanks,
Derek
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
I'm thinking that you may have your interest accured and interest paid terminology a little mixed up here.

Take a look at the following Quirky Update (QU) method for calculating what I think you need.


CREATE TABLE #Pmts
(tran_date DATETIME PRIMARY KEY CLUSTERED
,Calc_interest_accrued MONEY, Interest_paid MONEY
,TotalInterestPaid MONEY, TotalInterestAccrued MONEY
,TotalInterestDue AS (TotalInterestAccrued - TotalInterestPaid))

INSERT INTO #Pmts (tran_date, Calc_interest_accrued, Interest_paid
,TotalInterestPaid, TotalInterestAccrued)
SELECT '2012-09-08',54.06,NULL,0,0
UNION ALL SELECT '2012-09-10',54.06,NULL,0,0
UNION ALL SELECT '2012-09-11',24.04,112.35,0,0
UNION ALL SELECT '2012-09-13',23.67,20.12,0,0
UNION ALL SELECT '2012-09-14',23.67,NULL,0,0

DECLARE @InterestPaid MONEY, @InterestAccrued MONEY
SELECT @InterestPaid = 0, @InterestAccrued = 0

UPDATE #Pmts
SET TotalInterestPaid = ISNULL(@InterestPaid, 0) + TotalInterestPaid
,TotalInterestAccrued = TotalInterestAccrued + @InterestAccrued
,@InterestPaid = ISNULL(Interest_paid, 0) + @InterestPaid
,@InterestAccrued = @InterestAccrued + Calc_interest_accrued

SELECT * FROM #Pmts

DROP TABLE #Pmts




Note that QU requires the CLUSTERED index on your date column and it will be much faster than any recursive approach to solving this problem.

Even though it doesn't produce exactly the results you need, I'm hoping it puts you on the right track.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45116 Visits: 39917
dwain.c (10/28/2012)
I'm thinking that you may have your interest accured and interest paid terminology a little mixed up here.

Take a look at the following Quirky Update (QU) method for calculating what I think you need.


CREATE TABLE #Pmts
(tran_date DATETIME PRIMARY KEY CLUSTERED
,Calc_interest_accrued MONEY, Interest_paid MONEY
,TotalInterestPaid MONEY, TotalInterestAccrued MONEY
,TotalInterestDue AS (TotalInterestAccrued - TotalInterestPaid))

INSERT INTO #Pmts (tran_date, Calc_interest_accrued, Interest_paid
,TotalInterestPaid, TotalInterestAccrued)
SELECT '2012-09-08',54.06,NULL,0,0
UNION ALL SELECT '2012-09-10',54.06,NULL,0,0
UNION ALL SELECT '2012-09-11',24.04,112.35,0,0
UNION ALL SELECT '2012-09-13',23.67,20.12,0,0
UNION ALL SELECT '2012-09-14',23.67,NULL,0,0

DECLARE @InterestPaid MONEY, @InterestAccrued MONEY
SELECT @InterestPaid = 0, @InterestAccrued = 0

UPDATE #Pmts
SET TotalInterestPaid = ISNULL(@InterestPaid, 0) + TotalInterestPaid
,TotalInterestAccrued = TotalInterestAccrued + @InterestAccrued
,@InterestPaid = ISNULL(Interest_paid, 0) + @InterestPaid
,@InterestAccrued = @InterestAccrued + Calc_interest_accrued

SELECT * FROM #Pmts

DROP TABLE #Pmts




Note that QU requires the CLUSTERED index on your date column and it will be much faster than any recursive approach to solving this problem.

Even though it doesn't produce exactly the results you need, I'm hoping it puts you on the right track.


Not quite right. You need to modify the update to have a FROM clause from the target table so that you can add the MAXDOP option to prevent parallelism. Like this...

 UPDATE tgt
SET TotalInterestPaid = ISNULL(@InterestPaid, 0) + TotalInterestPaid
,TotalInterestAccrued = TotalInterestAccrued + @InterestAccrued
,@InterestPaid = ISNULL(Interest_paid, 0) + @InterestPaid
,@InterestAccrued = @InterestAccrued + Calc_interest_accrued
FROM #Pmts tgt WITH(TABLOCKX)
OPTION (MAXDOP 1)
;



The TablockX isn't required on a Temp Table but it'll bypass all sorts of row escalation to make leaner (memory-wise) and faster code.

I haven't checked the rest of the code nor have my changes made it solve the OP's precise problem.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
Jeff,

Thanks for keeping me honest. I must be rusty from my holiday away to have forgotten the MAXDOP and TABLOCK.

This should also work, no?


UPDATE #Pmts WITH(TABLOCK)
SET TotalInterestPaid = ISNULL(@InterestPaid, 0) + TotalInterestPaid
,TotalInterestAccrued = TotalInterestAccrued + @InterestAccrued
,@InterestPaid = ISNULL(Interest_paid, 0) + @InterestPaid
,@InterestAccrued = @InterestAccrued + Calc_interest_accrued
OPTION (MAXDOP 1)





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45116 Visits: 39917
Don't know. I've never tried it that way. I know the way I demonstrated works. Can't vouch for the way you showed at all.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search