Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Разнесение оплатыпо счетам  [new]
vma_mnt
Member

Откуда: Новокузнецк
Сообщений: 602
Возникла необходимость разнесения оплаты по счетам абонента. Пока сделали курсором. Пример ниже.

Можно ли как-то избежать курсора?

if object_id('Счета') is not null drop table Счета
if object_id('Деньги') is not null drop table Деньги
if object_id('СчетаДеньги') is not null drop table СчетаДеньги

create table Счета (КодСчета int identity primary key, КодАбонента int, Дата datetime, СуммаСчета decimal(18, 4))
create table Деньги (КодДенег int identity primary key, КодАбонента int, Дата datetime, СуммаДенег decimal(18, 4))
create table СчетаДеньги (КодСчета int, КодДенег int, СуммаРазнесения decimal(18, 4))

insert into Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090101', 100
insert into Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090201', 120
insert into Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090301', 130
insert into Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090401', 160
insert into Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090501', 900
insert into Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090601', 100
insert into Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090101', 100
insert into Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090201', 120
insert into Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090301', 130
insert into Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090401', 160
insert into Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090501', 900
insert into Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090601', 100
insert into Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090101', 100
insert into Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090201', 120
insert into Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090301', 130
insert into Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090401', 160
insert into Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090501', 900
insert into Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090601', 100

insert into Деньги (КодАбонента, Дата, СуммаДенег) select 1, '20090115', 200
insert into Деньги (КодАбонента, Дата, СуммаДенег) select 1, '20090315', 500
insert into Деньги (КодАбонента, Дата, СуммаДенег) select 1, '20090515', 810
insert into Деньги (КодАбонента, Дата, СуммаДенег) select 2, '20090115', 200
insert into Деньги (КодАбонента, Дата, СуммаДенег) select 2, '20090315', 400
insert into Деньги (КодАбонента, Дата, СуммаДенег) select 2, '20090515', 810
insert into Деньги (КодАбонента, Дата, СуммаДенег) select 3, '20090116', 200
insert into Деньги (КодАбонента, Дата, СуммаДенег) select 3, '20090316', 500
insert into Деньги (КодАбонента, Дата, СуммаДенег) select 3, '20090516', 510
go


declare @КодАбонента int, @СуммаСчета decimal(18, 4), @СуммаДенег decimal(18, 4), @СуммаНеразнесенная decimal(18, 4)
declare @КодСчета int, @КодДенег int

declare #Абоненты cursor for 
SELECT     КодАбонента
FROM         Счета
GROUP BY КодАбонента

open #Абоненты

fetch next from #Абоненты into @КодАбонента

while @@fetch_status = 0
begin

declare #Счета cursor for 
SELECT КодСчета, СуммаСчета FROM Счета WHERE (КодАбонента = @КодАбонента) ORDER BY Дата

open #Счета

declare #Деньги cursor for 
SELECT КодДенег, СуммаДенег FROM Деньги WHERE (КодАбонента = @КодАбонента) ORDER BY Дата

open #Деньги

fetch next from #Счета into @КодСчета, @СуммаСчета
if @@fetch_status <> 0 
begin
deallocate #Деньги
return
end

fetch next from #Деньги into @Кодденег, @СуммаДенег
if @@fetch_status <> 0 
begin
deallocate #Счета
deallocate #Деньги
return
end

while 1 = 1
begin --while 1 = 1

if @СуммаСчета < @СуммаДенег
begin --if @СуммаСчета < @СуммаДенег
insert into СчетаДеньги (КодСчета, КодДенег, СуммаРазнесения)
select @КодСчета, @КодДенег, @СуммаСчета

set @СуммаДенег = @СуммаДенег - @СуммаСчета

fetch next from #Счета into @КодСчета, @СуммаСчета
if @@fetch_status <> 0 
begin
deallocate #Счета
deallocate #Деньги
break
end

end --if @СуммаСчета < @СуммаДенег

else if @СуммаСчета = @СуммаДенег
begin --if @СуммаСчета = @СуммаДенег
insert into СчетаДеньги (КодСчета, КодДенег, СуммаРазнесения)
select @КодСчета, @КодДенег, @СуммаСчета

fetch next from #Счета into @КодСчета, @СуммаСчета
if @@fetch_status <> 0 
begin
deallocate #Счета
deallocate #Деньги
break
end

fetch next from #Деньги into @КодДенег, @СуммаДенег
if @@fetch_status <> 0 
begin
deallocate #Счета
deallocate #Деньги
break
end

end --if @СуммаСчета = @СуммаДенег

else if @СуммаСчета > @СуммаДенег
begin --if @СуммаСчета > @СуммаДенег
insert into СчетаДеньги (КодСчета, КодДенег, СуммаРазнесения)
select @КодСчета, @КодДенег, @СуммаДенег

set @СуммаСчета = @СуммаСчета - @СуммаДенег

fetch next from #Деньги into @КодДенег, @СуммаДенег
if @@fetch_status <> 0 
begin
deallocate #Счета
deallocate #Деньги
break
end

end --if @СуммаСчета > @СуммаДенег

end --while 1 = 1

deallocate #Счета

deallocate #Деньги

fetch next from #Абоненты into @КодАбонента

end 

deallocate  #Абоненты
go

select * from СчетаДеньги

drop table Счета
drop table Деньги
drop table СчетаДеньги
29 ноя 09, 11:07    [7993119]     Ответить | Цитировать Сообщить модератору
 Re: Разнесение оплатыпо счетам  [new]
aleks2
Guest
Хе-хе...
declare @Счета table(КодСчета int identity primary key, КодАбонента int, Дата datetime, СуммаСчета decimal(18, 4))
declare @Деньги table(КодДенег int identity primary key, КодАбонента int, Дата datetime, СуммаДенег decimal(18, 4))

insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090101', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090201', 120
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090301', 130
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090401', 160
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090501', 900
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090601', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090101', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090201', 120
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090301', 130
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090401', 160
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090501', 900
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090601', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090101', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090201', 120
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090301', 130
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090401', 160
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090501', 900
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090601', 100

insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 1, '20090115', 200
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 1, '20090315', 500
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 1, '20090515', 810
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 2, '20090115', 200
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 2, '20090315', 400
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 2, '20090515', 810
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 3, '20090116', 200
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 3, '20090316', 500
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 3, '20090516', 510

;
with am(КодАбонента, НомСчПП, НомДенПП, ДатаСчета, ДатаДенег, ОстСчета, ОстДенег, ЗачтеноНаСчет, СписаноДенег)
as (
select КодАбонента, 0 НомСчПП,  0 НомДенПП, cast(0 as datetime) ДатаСчета, cast(0 as datetime) ДатаДенег
, cast(0 as decimal(18, 4)) ОстСчета, cast(0 as decimal(18, 4)) ОстДенег
, cast(0 as decimal(18, 4)) ЗачтеноНаСчет, cast(0 as decimal(18, 4)) СписаноДенег
from @Счета 
GROUP BY КодАбонента
UNION ALL
select x.КодАбонента, x.НомСчПП,  x.НомДенПП, x.ДатаСчета, x.ДатаДенег
	, cast(case WHEN x.ОстСчета>=x.ОстДенег THEN x.ОстСчета-x.ОстДенег ELSE 0 END as decimal(18, 4)) ОстСчета
	, cast(case WHEN x.ОстСчета<=x.ОстДенег THEN x.ОстДенег-x.ОстСчета ELSE 0 END as decimal(18, 4)) ОстДенег
	, cast(case WHEN x.ОстСчета>=x.ОстДенег THEN x.ОстДенег ELSE x.ОстСчета END as decimal(18, 4)) ЗачтеноНаСчет
	, cast(case WHEN x.ОстСчета<=x.ОстДенег THEN x.ОстСчета ELSE x.ОстДенег END as decimal(18, 4)) СписаноДенег
FROM 
(
select am.КодАбонента
	, case am.ОстСчета WHEN cast(0 as decimal(18, 4)) then am.НомСчПП+1 ELSE am.НомСчПП END НомСчПП
	, case am.ОстДенег WHEN cast(0 as decimal(18, 4)) then am.НомДенПП+1 ELSE am.НомДенПП END НомДенПП
	, case am.ОстСчета WHEN cast(0 as decimal(18, 4)) then a.Дата ELSE am.ДатаСчета END ДатаСчета
	, case am.ОстДенег WHEN cast(0 as decimal(18, 4)) then m.Дата ELSE am.ДатаДенег END ДатаДенег
	, case am.ОстСчета WHEN cast(0 as decimal(18, 4)) then a.СуммаСчета ELSE am.ОстСчета END ОстСчета
	, case am.ОстДенег WHEN cast(0 as decimal(18, 4)) then m.СуммаДенег ELSE am.ОстДенег END ОстДенег
FROM am
iNNER join
(select *, row_number() OVER(PARTITION BY КодАбонента ORDER BY Дата) НомСчПП FROM @Счета) a
ON am.КодАбонента=a.КодАбонента AND am.НомСчПП+1=a.НомСчПП
iNNER join
(select *, row_number() OVER(PARTITION BY КодАбонента ORDER BY Дата) НомДенПП FROM @Деньги) m
ON am.КодАбонента=m.КодАбонента AND am.НомДенПП+1=m.НомДенПП
--WHERE a.КодАбонента is not null or m.КодАбонента is not null
) x
)
select * FROM am where НомСчПП>0
29 ноя 09, 12:27    [7993207]     Ответить | Цитировать Сообщить модератору
 Re: Разнесение оплатыпо счетам  [new]
vma_mnt
Member

Откуда: Новокузнецк
Сообщений: 602
aleks2, огромное спасибо. Проблема наполовину решилась.

а можно ли в строку
with am(КодАбонента, НомСчПП, НомДенПП, ДатаСчета, ДатаДенег, ОстСчета, ОстДенег, ЗачтеноНаСчет, СписаноДенег)

добавить поля КодСчета и КодДенег?
29 ноя 09, 13:44    [7993301]     Ответить | Цитировать Сообщить модератору
 Re: Разнесение оплатыпо счетам  [new]
aleks2
Guest
vma_mnt
aleks2, огромное спасибо. Проблема наполовину решилась.

а можно ли в строку
with am(КодАбонента, НомСчПП, НомДенПП, ДатаСчета, ДатаДенег, ОстСчета, ОстДенег, ЗачтеноНаСчет, СписаноДенег)

добавить поля КодСчета и КодДенег?


Можно. Я разрешаю.
29 ноя 09, 14:35    [7993366]     Ответить | Цитировать Сообщить модератору
 Re: Разнесение оплатыпо счетам  [new]
vma_mnt
Member

Откуда: Новокузнецк
Сообщений: 602
aleks2

Можно. Я разрешаю.


Спасибо, попробую:)
29 ноя 09, 14:43    [7993380]     Ответить | Цитировать Сообщить модератору
 Re: Разнесение оплатыпо счетам  [new]
vma_mnt
Member

Откуда: Новокузнецк
Сообщений: 602
Попробовал. На 500 записях работает значительно дольше курсора. А надо 30 000 записей.
30 ноя 09, 06:45    [7994560]     Ответить | Цитировать Сообщить модератору
 Re: Разнесение оплатыпо счетам  [new]
aleks2
Guest
vma_mnt
Попробовал. На 500 записях работает значительно дольше курсора. А надо 30 000 записей.


За скорость - базара не было.

Естественно... тута требуется изрядная оптимизация.

1. ROW_NUMBER() надо убирать.
30 ноя 09, 06:55    [7994564]     Ответить | Цитировать Сообщить модератору
 Re: Разнесение оплатыпо счетам  [new]
aleks2
Guest
Что-то вроде этого

declare @Счета table(КодСчета int identity primary key, КодАбонента int, Дата datetime, СуммаСчета decimal(18, 4))
declare @Деньги table(КодДенег int identity primary key, КодАбонента int, Дата datetime, СуммаДенег decimal(18, 4))

insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090101', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090201', 120
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090301', 130
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090401', 160
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090501', 900
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 1, '20090601', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090101', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090201', 120
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090301', 130
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090401', 160
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090501', 900
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 2, '20090601', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090101', 100
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090201', 120
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090301', 130
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090401', 160
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090501', 900
insert into @Счета (КодАбонента, Дата, СуммаСчета) select 3, '20090601', 100

insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 1, '20090115', 200
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 1, '20090315', 500
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 1, '20090515', 810
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 2, '20090115', 200
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 2, '20090315', 400
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 2, '20090515', 810
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 3, '20090116', 200
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 3, '20090316', 500
insert into @Деньги (КодАбонента, Дата, СуммаДенег) select 3, '20090516', 510

declare @СчетаX table(n int identity, КодСчета int , КодАбонента int, Дата datetime, СуммаСчета decimal(18, 4)
,primary key clustered(КодАбонента, n))
declare @ДеньгиX table(n int identity, КодДенег int , КодАбонента int, Дата datetime, СуммаДенег decimal(18, 4)
,primary key clustered(КодАбонента, n))

insert @СчетаX(КодСчета, КодАбонента, Дата, СуммаСчета)
select КодСчета, КодАбонента, Дата, СуммаСчета
FROM @Счета
ORDER BY КодАбонента, Дата

insert @ДеньгиX (КодДенег, КодАбонента, Дата, СуммаДенег)
select КодДенег, КодАбонента, Дата, СуммаДенег
FROM @Деньги
ORDER BY КодАбонента, Дата

SET STATISTICS IO ON 
;
with 
am(КодАбонента, НомСчПП, НомДенПП, ДатаСчета, ДатаДенег, ОстСчета, ОстДенег, ЗачтеноНаСчет, СписаноДенег)
as (
select aX.КодАбонента, НомСчПП,  НомДенПП, cast(0 as datetime) ДатаСчета, cast(0 as datetime) ДатаДенег
, cast(0 as decimal(18, 4)) ОстСчета, cast(0 as decimal(18, 4)) ОстДенег
, cast(0 as decimal(18, 4)) ЗачтеноНаСчет, cast(0 as decimal(18, 4)) СписаноДенег
from 
(select КодАбонента, min(n)-1 НомСчПП from @СчетаX GROUP BY КодАбонента) aX
LEFT OUTER JOIN
(select КодАбонента, min(n)-1 НомДенПП from @ДеньгиX GROUP BY КодАбонента) mX
ON aX.КодАбонента=mX.КодАбонента
UNION ALL
select x.КодАбонента, x.НомСчПП,  x.НомДенПП, x.ДатаСчета, x.ДатаДенег
	, case WHEN x.ОстСчета>=x.ОстДенег THEN cast(x.ОстСчета-x.ОстДенег as decimal(18, 4)) ELSE 0 END  ОстСчета
	, case WHEN x.ОстСчета<=x.ОстДенег THEN cast(x.ОстДенег-x.ОстСчета as decimal(18, 4)) ELSE 0 END ОстДенег
	, case WHEN x.ОстСчета>=x.ОстДенег THEN x.ОстДенег ELSE x.ОстСчета END ЗачтеноНаСчет
	, case WHEN x.ОстСчета<=x.ОстДенег THEN x.ОстСчета ELSE x.ОстДенег END СписаноДенег
FROM 
(
select am.КодАбонента
	, case am.ОстСчета WHEN 0 then am.НомСчПП+1 ELSE am.НомСчПП END НомСчПП
	, case am.ОстДенег WHEN 0 then am.НомДенПП+1 ELSE am.НомДенПП END НомДенПП
	, case am.ОстСчета WHEN 0 then a.Дата ELSE am.ДатаСчета END ДатаСчета
	, case am.ОстДенег WHEN 0 then m.Дата ELSE am.ДатаДенег END ДатаДенег
	, case am.ОстСчета WHEN 0 then a.СуммаСчета ELSE am.ОстСчета END ОстСчета
	, case am.ОстДенег WHEN 0 then m.СуммаДенег ELSE am.ОстДенег END ОстДенег
FROM am
iNNER join
@СчетаX a
ON am.КодАбонента=a.КодАбонента AND am.НомСчПП+1=a.n
iNNER join
@ДеньгиX m
ON am.КодАбонента=m.КодАбонента AND am.НомДенПП+1=m.n
) x
)
select * FROM am where НомСчПП>0
спасет отца русской дерьмократии...
30 ноя 09, 07:20    [7994576]     Ответить | Цитировать Сообщить модератору
 Re: Разнесение оплатыпо счетам  [new]
vma_mnt
Member

Откуда: Новокузнецк
Сообщений: 602
Спасибо, попробую, напишу результат
30 ноя 09, 07:25    [7994581]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить