Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recursive RunningTotal calculate Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 7:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 11:07 PM
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
Post #1376740
Posted Sunday, October 28, 2012 7:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
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!
Post #1378080
Posted Sunday, October 28, 2012 9:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1378085
Posted Sunday, October 28, 2012 9:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
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!
Post #1378086
Posted Sunday, October 28, 2012 9:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1378089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse