December 19, 2011 at 9:17 am
I sure hope someone will be able to help me with this problem.
Here is the situation:
I have a table holding transaction data: money owed / money paid
The vast majority link together, however, a small % do not.
I need to apply money paid to money owed.
I want to give the client the benefit of the doubt, while using some logic in the application of funds.
I know when the transactions occurred, so I decided to work from newest to oldest, applying available funds from any money paid that is newer than the money owed.
When the process completes the look back for an account, there may be money paid left over. This is okay, as it could apply to money owed transactions prior to the look back period.
I have code in place to accomplish this. My problem is the amount of time the process takes to run.
At the current pace, it will take well over a day to complete.
Here is an example of what the data may look like for one account:
TranDate TranType Amount
01/05/2008Owed $50.00
01/24/2008Paid$75.00
04/15/2008 Owed $43.75
04/28/2008Paid $50.00
05/23/2008Owed$43.75
08/19/2008Owed $35.20
02/10/2009Paid$14.15
03/16/2009Paid $9.50
06/14/2009Owed $63.45
09/24/2009Paid$40.00
Here is what I want to accomplish:
OwedTranDateTtlOwed PaidTranDatePaidTranAmtPaidAmtAppliedPaidAmtRemainingOwedAmtRemaining
06/14/2009$63.4509/24/2009$40.00 $40.00$0.00$23.45
08/19/2008$35.2003/16/2009$9.50$9.50$0.00$25.70
08/19/2008$35.2002/10/2009$14.15$14.15$0.00$21.05
05/23/2008$43.75$43.75
04/15/2008$43.7504/28/2008$50.00$43.75$6.25$0.00
01/05/2008$50.0004/28/2008$6.25$6.25$0.00$43.75
01/05/2008$43.7501/24/2008$75.00$43.75$31.25$0.00
Here is the code to accomplish the look back:
DECLARE
@AcctNumDECIMAL(10,0),
@TranDateDECIMAL(8,0),
@TranSeqDECIMAL(4,0),
@TranODINT,
@TranTypeVARCHAR(20),
@TranAmtDECIMAL(7,2),
@MatchSeqDECIMAL(4,0),
@MatchAmtDECIMAL(7,2),
@TRZTDECIMAL(7,2),
@TRMTDECIMAL(7,2),
@RemainingAmtDECIMAL(7,2)
DECLARE ZERO CURSOR FAST_FORWARD
FOR
SELECT DISTINCT L.AcctNum, L.TranDate, L.TranSeq, L.TranType, ABS(L.Amount) Amount
FROM LFR2Trans (NOLOCK) L, LFR2FundsApplied M
WHERE L.AcctNum= M.AcctNum
AND (M.UsedUp IS NULL OR M.UsedUp NOT IN('Y', 'D'))
AND L.DueDate = 0
AND TranType = 'Paid'
ORDER BY L.AcctNum, L.TranSeq DESC
OPEN ZERO
FETCH NEXT FROM ZERO
INTO @AcctNum, @TranDate, @TranSeq, @TranType, @TranAmt
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TRZT = @TranAmt
DECLARE MATCH CURSOR --LOCAL STATIC
FOR
SELECT TranSeq, Assessed - ISNULL(AmtApplied,0)
FROM LFR2FundsApplied
WHERE AcctNum= @AcctNum
AND (TranDate <= @TranDate OR TranSeq < @TranSeq)
AND (UsedUp IS NULL OR UsedUp NOT IN('Y', 'D'))
ORDER BY TranSeq DESC
OPEN MATCH
FETCH NEXT FROM MATCH
INTO @MatchSeq, @MatchAmt
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TRMT = NULL
SELECT TOP 1 @TRMT = MatchAmtRemaining
FROM TranMatch
WHERE AcctNum= @AcctNum
AND MatchSeq = @MatchSeq
ORDER BY TranSeq
SET @TRMT = ISNULL(@TRMT,@MatchAmt)
SET @RemainingAmt = CASE WHEN @TRMT - @TRZT < 0
THEN 0
ELSE @TRMT - @TRZT
END
INSERT INTO TranMatch (AcctNum, TranSeq, MatchSeq, MatchAmtRemaining, TranAmtApplied)
VALUES (@AcctNum, @TranSeq, @MatchSeq, @RemainingAmt, CASE WHEN @RemainingAmt > 0 THEN @TRZT ELSE @TRMT END)
IF @RemainingAmt = 0
BEGIN
UPDATE LFR2FundsApplied
SET UsedUp = 'D'
WHERE AcctNum= @AcctNum
AND TranSeq = @MatchSeq
END
IF @TRMT - @TRZT < 0
BEGIN
SET @TRZT = @TRZT - @TRMT
END
ELSE
BEGIN
BREAK
END
FETCH NEXT FROM MATCH
INTO @MatchSeq, @MatchAmt
END
CLOSE MATCH
DEALLOCATE MATCH
FETCH NEXT FROM ZERO
INTO @AcctNum, @TranDate, @TranSeq, @TranType, @TranAmt
END
CLOSE ZERO
DEALLOCATE ZERO
I am sure this code is a mess.
Any help tweaking this code would be appreciated.
However, I really hope there is a better approach.
Thanks.
December 19, 2011 at 9:50 am
You want to totally scrap your method and start over using a set-based approach. CURSORS are as slow as molasses in January.
This is essentially a reverse running total. (Instead of starting at zero and adding amounts, you're starting from an amount and decrementing to zero.) Jeff Moden has a very good article on Solving the Running Total and Ordinal Rank Problems (Rewritten)[/url]. Make sure that you meet all of the conditions specified in that article and also check the discussion for that article, since the discussion contains some improvements that may not have been incorporated back into the article.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 19, 2011 at 9:55 am
Thanks. I will have a look.
I know to avoid cursors, but just couldn't get my head around how to do it without one (or two in this case).
December 19, 2011 at 9:56 am
Hi Doug, welcome to the forum. This is a Running Total project, and there are several ways of getting the output you're expecting. The fastest is the Quirky Update (QU), I'll post a link for that shortly. The easiest is to use a recursive CTE (rCTE), which is about five times slower than a QU but makes a good testbench for the arithmetic, which can then be reengineered into a QU. You can also use cursors but the performance is awful, as you've found - and triangular joins, which scale very badly unless the data consists of relatively small partitions which are aggregated independently. Before we start, can you please read the link below (please read this). It will show you amongst other things how to post readily-consumable data for folks to test against.
Cheers
Edit: Drew beat me to it - again 😎
The Quirky Update is described in the link he's posted.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 19, 2011 at 12:14 pm
Drew:
Wow! The Quirky Update is totally awesome!
I am off to code. I'll let you know how it goes.
Chris:
I will certainly keep in mind your suggestions when I post issues in the future.
December 20, 2011 at 8:12 am
The Quirky Update seems to be doing the trick very nicely. I still have some further testing, however, the initial results look correct.
My original code has been running for over a day now and looks like it will take 3 more days to complete.
With the Quirky Update, I was able to process 1.6 million records in 25 seconds.
Here is the code as copied and updated from the article:
/*************************************************************************************
Pseduo-cursor update using the "Quirky Update" to calculate both Running Totals and
a Running Count that start over for each AccountID.
Takes 24 seconds with the INDEX(0) hint and 6 seconds without it on my box.
*************************************************************************************/
--===== Supress the auto-display of rowcounts for speed and appearance
SET NOCOUNT ON
--===== Declare the working variables
DECLARE
@PrevAccountIDINT,
@AccountRunningTotalDECIMAL(9,2),
@AssessedAmtLeftDECIMAL(9,2),
@PaidRunningTotalDECIMAL(9,2),
@WaivedRunningTotalDECIMAL(9,2),
@PaidAmtAppliedDECIMAL(9,2),
@WaivedAmtAppliedDECIMAL(9,2)
--===== Update the running total and running count for this row using the "Quirky
-- Update" and a "Pseudo-cursor". The order of the UPDATE is controlled by the
-- order of the clustered index.
UPDATE LFR2LookBack
SET @PaidAmtApplied = PdAmtApplied = CASE WHEN AcctNum = @PrevAccountID
THEN CASE WHEN TranType = 'Owed'
THEN CASE WHEN Amount - @PaidRunningTotal <= 0
THEN Amount
ELSE @PaidRunningTotal
END
ELSE 0
END
END,
@WaivedAmtApplied = WvAmtApplied = CASE WHEN AcctNum = @PrevAccountID
THEN CASE WHEN TranType = 'Owed'
THEN CASE WHEN Amount - @PaidAmtApplied = 0
THEN 0
ELSE CASE WHEN Amount - @PaidAmtApplied - @WaivedRunningTotal <= 0
THEN Amount - @PaidAmtApplied
ELSE @WaivedRunningTotal
END
END
ELSE 0
END
END,
@AssessedAmtLeft = AssessedAmtLeft = CASE WHEN AcctNum = @PrevAccountID
THEN CASE WHEN TranType = 'Owed'
THEN Amount - @PaidAmtApplied - @WaivedAmtApplied
ELSE 0
END
ELSE CASE WHEN TranType = 'Owed'
THEN Amount
ELSE 0
END
END,
@PaidRunningTotal = CASE WHEN AcctNum = @PrevAccountID
THEN CASE WHEN TranType = 'Owed'
THEN @PaidRunningTotal - @PaidAmtApplied
ELSE CASE WHEN TranType = 'Paid'
THEN @PaidRunningTotal + Amount
ELSE @PaidRunningTotal
END
END
ELSE CASE WHEN TranType = 'Paid'
THEN Amount
ELSE 0
END
END,
@WaivedRunningTotal = CASE WHEN AcctNum = @PrevAccountID
THEN CASE WHEN TranType = 'Owed'
THEN @WaivedRunningTotal - @WaivedAmtApplied
ELSE CASE WHEN TranType = 'Waived'
THEN @WaivedRunningTotal + Amount
ELSE @WaivedRunningTotal
END
END
ELSE CASE WHEN TranType = 'Waived'
THEN Amount
ELSE 0
END
END,
@AccountRunningTotal = PdWvAmtAvail = CASE WHEN AcctNum = @PrevAccountID
THEN CASE WHEN TranType = 'Owed'
THEN ((@AccountRunningTotal - Amount) + ABS(@AccountRunningTotal - Amount))/2
ELSE @AccountRunningTotal + Amount
END
ELSE CASE WHEN TranType = 'Owed'
THEN 0
ELSE Amount
END
END,
@PrevAccountID = LoanNum
FROM LFR2LookBack WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
Thanks so much for the help and directing me to Jeff's article and thanks Jeff for providing this information.
I will definitely be spending more time on this site to improve my knowledge of SQL.
Doug
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply