Look Back Through Records to Apply Funds Appropria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Look Back Through Records to Apply Funds Appropria

Author

Topic

DougG
Starting Member

1 Post

Posted - 2011-12-19 : 11:37:10

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

Here is the code to accomplish the look back:


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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources