Sql-server – Performing a “Spread Negitive” over a dataset

cursorsoptimizationsql serversql-server-2005

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

Will become:

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.

Best Answer

Often with operations like running totals it is actually more efficient to use a cursor than some of the other methods. Don't be afraid of using a cursor unless you are seeing noticeable performance issues.

In SQL Server 2012 there are new window functions that make this better, but obviously you can't use those. There is a quirky update approach that "works" but the syntax isn't officially supported and might not work post-SQL Server 2012 - it may someday become illegal syntax and there is never any guarantee about how the ordering works. The typical set-based approaches don't scale well - with a cursor it is often better because you only have to scan each row once, whereas with a set-based solution the scanning grows non-linearly. I wish I could show you the work I've been doing on this, but the publication date of the blog post that will reveal it all is still unknown.

At the very least when you declare a cursor use:

DECLARE ... CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY

This also might be useful reading:

Related Question