Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    1

    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:

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •