Я пытаюсь написать запрос, который будет проходить через таблицу и применять любые кредиты, которые есть на счете, к самому старому балансу . Я не мог придумать способ сделать это без использования курсора, и я знаю, что курсоров следует избегать любой ценой, если это возможно, поэтому я обращаюсь за помощью.
select * into #balances from [IDAT_AR_BALANCES] where amount > 0
select * into #credits from [IDAT_AR_BALANCES] where amount < 0
create index ba_ID on #balances (CLIENT_ID)
create index cr_ID on #credits (CLIENT_ID)
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(@credit < 0 and (select count(*) from #balances where @client_id = CLIENT_ID and amount <> 0) > 0)
begin
select top 1 @balance = amount, @cvtGuidBalance = cvtGuid from #balances where @client_id = CLIENT_ID and amount <> 0 order by AGING_DATE
set @credit = @balance + @credit
if(@credit > 0)
begin
update #balances set amount = @credit where cvtGuid = @cvtGuidBalance
set @credit = 0
end
else
begin
update #balances set amount = 0 where cvtGuid = @cvtGuidBalance
end
end
update #credits set amount = @credit where cvtGuid = @cvtGuidCredit
fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit
end
close credit_cursor
deallocate credit_cursor
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
В моих тестовых случаях для 10000 записей и 1000 клиентов это на выполнение потребуется 26 секунд, добавив два индекса к CLIENT_ID, я смог уменьшить число до 14 секунд. Однако это все еще слишком медленно для того, что мне нужно, конечный результат может иметь до 10 000 клиентов и более 4 000 000 записей, поэтому время выполнения может легко выражаться в минутах, выражаемых двузначными числами.
Мы будем очень благодарны за любые рекомендации о том, как я могу реструктурировать это для удаления курсора.
Пример ( обновлен, чтобы показать, что у вас может быть несколько кредитов после его запуска ):
before
cvtGuid client_id ammount AGING_DATE
xxxxxx 1 20.00 1/1/2011
xxxxxx 1 30.00 1/2/2011
xxxxxx 1 -10.00 1/3/2011
xxxxxx 1 5.00 1/4/2011
xxxxxx 2 20.00 1/1/2011
xxxxxx 2 15.00 1/2/2011
xxxxxx 2 -40.00 1/3/2011
xxxxxx 2 5.00 1/4/2011
xxxxxx 3 10.00 1/1/2011
xxxxxx 3 -20.00 1/2/2011
xxxxxx 3 5.00 1/3/2011
xxxxxx 3 -8.00 1/4/2011
after
cvtGuid client_id ammount AGING_DATE
xxxxxx 1 10.00 1/1/2011
xxxxxx 1 30.00 1/2/2011
xxxxxx 1 5.00 1/4/2011
xxxxxx 3 -5.00 1/2/2011
xxxxxx 3 -8.00 1/4/2011
, поэтому отрицательный кредит будет применен к самому старому положительному сальдо ( клиент 1 в примере ), если после этого нет оставшихся положительных балансов, он оставляет оставшиеся отрицательные ( клиент 3 ), если они полностью компенсируются (это в 90% случаев с реальными данными) он полностью удалит запись ( клиент 2 ).