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:
Code:
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:
Code:
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
Here is the code to accomplish the look back:
Code:
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
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.
dBforums
>
Look Back Through Records to Apply Funds Appropriately
12-19-11, 11:32
Linear Mode
