Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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: http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx

share|improve this answer
add comment

Thanks to Aaron's suggestions, instead of trying to make the entire thing set based I just tried to do sets where sets would apply best then use a cursor for the rest.

I also was able to alter the requirement of having to only pull from the oldest credit, now I can sum the credits in to one bucket per client and work against that.

It is much faster now, here is the updated version

--indexes to speed up first two queires
IF not EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_AR_BALANCES]') AND name = N'IX_IDATARBALANCES_CLIENTID_GUID')
    CREATE NONCLUSTERED INDEX IX_IDATARBALANCES_CLIENTID_GUID
    ON [dbo].[IDAT_AR_BALANCES] ([CLIENT_ID])
    INCLUDE ([cvtGUID])

IF not EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_AR_BALANCES]') AND name = N'IX_IDATARBALANCES_AMOUNT_ID_DATE')   
    CREATE NONCLUSTERED INDEX [IX_IDATARBALANCES_AMOUNT_ID_DATE]
    ON [dbo].[IDAT_AR_BALANCES] ([AMOUNT])
    INCLUDE ([CLIENT_ID],[AGING_DATE])

--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)

--find all instances that credit > balance
SELECT newid() as cvtGUID, client_id, max(AGING_DATE) as AGING_DATE, sum(AMOUNT) as amount
into #creditLarger
FROM IDAT_AR_BALANCES 
GROUP BY client_id 
HAVING SUM(amount) < 0

--remove all of the creditLargerEntries
delete IDAT_AR_BALANCES where client_id in (select client_id from #creditLarger)

--Build a list of remaining balances and summed credits
select * into #balances from [IDAT_AR_BALANCES] where amount > 0

SELECT newid() as cvtGUID, client_id, max(AGING_DATE) as AGING_DATE, sum(AMOUNT) as amount
into #credits 
FROM [IDAT_AR_BALANCES] 
where amount < 0 
GROUP BY client_id

--Index to make the update faster
CREATE NONCLUSTERED INDEX BALANCE_INDEX ON #balances ([CLIENT_ID],[AMOUNT])

--Begin loop of processing credits
set nocount on
declare credit_cursor cursor LOCAL STATIC READ_ONLY FORWARD_ONLY for select top 10 [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 exists(select * from #balances where @client_id = CLIENT_ID and amount > 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(@credit > 0) 
    begin 
      --If the credit is now postive save the leftover in the currently selected balance and set the credit to 0
      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
set nocount off

truncate table [IDAT_AR_BALANCES]

insert into [IDAT_AR_BALANCES] 
    select * from #balances
    union select * from #credits
    union select * from #creditLarger

--Delete any balances and credits that where 0'ed out durning the spred negitive.
delete [IDAT_AR_BALANCES] where amount = 0

drop table #balances
drop table #credits
drop table #creditLarger
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.