I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest first, but not required) to all outstanding balances (in order of oldest first).
For example (a negative balance represents a credit)
Account_ID DateOfEntry Balance ---------- ----------- ------- 1 1/1/2012 10.00 1 1/2/2012 -15.00 2 1/1/2012 -15.00 2 1/2/2012 10.00 3 1/1/2012 10.00 3 1/2/2012 1.00 3 1/3/2012 -5.00 4 1/1/2012 5.00 4 1/2/2012 5.00 4 1/3/2012 -7.00 5 1/1/2012 10.00 5 1/2/2012 -5.00 5 1/3/2012 -5.00
Account_ID DateOfEntry Balance ---------- ----------- ------- 1 1/2/2012 -5.00 2 1/1/2012 -5.00 3 1/1/2012 5.00 3 1/2/2012 1.00 4 1/2/2012 2.00
Here is a breakdown of what happened
- Account 1 and 2 have the credit left behind (demonstrates that the order of payment and credit relative to each other does not matter)
- Account 3 has the two balances left behind (demonstrates credits are applied to the oldest balance first)
- Account 4 has one balance left on 1/2/2012 (demonstrates credits are applied to the next oldest balance if the first one does not satisfy the credit)
- Account 5 is gone because the credits perfectly match the balances.
Here is the schema of the relevent columns in my real table
CREATE TABLE [dbo].[IDAT_AR_BALANCES]( [cvtGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [CLIENT_ID] [varchar](11) NOT NULL, [AGING_DATE] [datetime] NOT NULL, [AMOUNT] [money] NOT NULL, CONSTRAINT [PK_IDAT_ARBALANCES] PRIMARY KEY CLUSTERED ([cvtGUID] ASC) )
Currently I do this with a cursor looping through all of the available credits.
--Remove AR that totals to 0. DELETE FROM IDAT_AR_BALANCES WHERE client_id IN ( SELECT client_id FROM IDAT_AR_BALANCES GROUP BY client_id HAVING SUM(amount) = 0) --Spred the credits on to existing balances. select * into #balances from [IDAT_AR_BALANCES] where amount > 0 select * into #credits from [IDAT_AR_BALANCES] where amount < 0 declare credit_cursor cursor for select [CLIENT_ID], amount, cvtGUID from #credits open credit_cursor declare @client_id varchar(11) declare @credit money declare @balance money declare @cvtGuidBalance uniqueidentifier declare @cvtGuidCredit uniqueidentifier fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit while @@fetch_status = 0 begin --While balances exist for the current client_ID and there are still credits to be applied, loop. while(@credit < 0 and (select count(*) from #balances where @client_id = CLIENT_ID and amount <> 0) > 0) begin --Find the oldest oustanding balance. select top 1 @balance = amount, @cvtGuidBalance = cvtGuid from #balances where @client_id = CLIENT_ID and amount <> 0 order by AGING_DATE -- merge the balance and the credit set @credit = @balance + @credit --If the credit is now postive save the leftover in the currently selected balance and set the credit to 0 if(@credit > 0) begin update #balances set amount = @credit where cvtGuid = @cvtGuidBalance set @credit = 0 end else -- Credit is larger than the balance, 0 out the balance and continue processesing update #balances set amount = 0 where cvtGuid = @cvtGuidBalance end -- end of while loop --There are no more balances to apply the credit to, save it back to the list. update #credits set amount = @credit where cvtGuid = @cvtGuidCredit --Get the next credit. fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit end close credit_cursor deallocate credit_cursor --Delete any balances and credits that where 0'ed out durning the spred negitive. delete #balances where AMOUNT = 0 delete #credits where AMOUNT = 0 truncate table [IDAT_AR_BALANCES] insert [IDAT_AR_BALANCES] select * from #balances insert [IDAT_AR_BALANCES] select * from #credits drop table #balances drop table #credits
I am sure there are better ways to do this so I can do this without the cursor and get better performance out of it, but I am having difficulty on figuring out how I can fulfill the "use the oldest date first" requirement without using a cursor.