12-19-11, 11:32 #1Registered User
- Join Date
- Dec 2011
Unanswered: Look Back Through Records to Apply Funds Appropriately
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/2008 Owed $50.00 01/24/2008 Paid $75.00 04/15/2008 Owed $43.75 04/28/2008 Paid $50.00 05/23/2008 Owed $43.75 08/19/2008 Owed $35.20 02/10/2009 Paid $14.15 03/16/2009 Paid $9.50 06/14/2009 Owed $63.45 09/24/2009 Paid $40.00
Here is what I want to accomplish:
OwedTranDate TtlOwed PaidTranDate PaidTranAmt PaidAmtApplied PaidAmtRemaining OwedAmtRemaining 06/14/2009 $63.45 09/24/2009 $40.00 $40.00 $0.00 $23.45 08/19/2008 $35.20 03/16/2009 $9.50 $9.50 $0.00 $25.70 08/19/2008 $35.20 02/10/2009 $14.15 $14.15 $0.00 $21.05 05/23/2008 $43.75 $43.75 04/15/2008 $43.75 04/28/2008 $50.00 $43.75 $6.25 $0.00 01/05/2008 $50.00 04/28/2008 $6.25 $6.25 $0.00 $43.75 01/05/2008 $43.75 01/24/2008 $75.00 $43.75 $31.25 $0.00
DECLARE @AcctNum DECIMAL(10,0), @TranDate DECIMAL(8,0), @TranSeq DECIMAL(4,0), @TranOD INT, @TranType VARCHAR(20), @TranAmt DECIMAL(7,2), @MatchSeq DECIMAL(4,0), @MatchAmt DECIMAL(7,2), @TRZT DECIMAL(7,2), @TRMT DECIMAL(7,2), @RemainingAmt DECIMAL(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
Any help tweaking this code would be appreciated.
However, I really hope there is a better approach.
12-19-11, 13:50 #2World Class Flame Warrior
Provided Answers: 1
- Join Date
- Jun 2003
Embedded cursors are nasty, though at least one cursor will probably be required in order to do this.
Unfortunately, there is really not enough description of your problem in order to help you.
The gap between "Here is an example of what the data may look like for one account:" and "Here is what I want to accomplish:" is pretty large.