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

Откуда: Москва
Сообщений: 311
Задача стоим следующим образом:

Есть список клиентов @ListClient
idclient - его ID
Summa - ту сумму которую необходимо списать.

Есть список его счетов: @listaccbalance

idclient - id клиента
account - номер счета
summaost - сумма остатка
priority - приоритет списания (от 1 до бесконечности)

На выходе нужно для каждого клиента "расписать" сумму для списания с каждого счета.

DECLARE @ListClient TABLE (idclient int, summa numeric(16,2))

INSERT INTO @listclient
SELECT 1, 500
UNION
SELECT 2, 1000
UNION
SELECT 3, 200
UNION
SELECT 4, 50
UNION
SELECT 5, 700

DECLARE @ListAccBalance TABLE (idclient int, account varchar(20), summaost numeric(16,2), priority int)

INSERT INTO @ListAccBalance
SELECT 1,'1', 600, 1
UNION
SELECT 2,'21',1000,3
UNION
SELECT 2,'22',400,2
UNION
SELECT 2,'23',300,1
UNION
SELECT 3,'3',200,1
UNION
SELECT 4,'4',20,1
UNION
SELECT 5,'5',0,1


Т.е. прежде чем расписывать сумму нужно отсортировать список для каждого клиента по приоритету и затем размазывать сумму.

На выходе нам нужно получить вот такую табличку:

SummaSpis - сумма списания
Comment - для понимания логики работы

IDclientAccountSummaClientPrioritySummaSpisComment
116001500Нужно списать 500 т.к. на счет 600 нам нужно только 500
22310001300Нужно списать 1000 но на счету только 300 списываем 300
22210002400Нужно списать 1000-300=700 но на счету только 400 списываем 400
22310003300Нужно списать 1000-300-400=300 но на счету 1000 нам столько не надо списываем 300
332001200Нужно 200 и на счету 200 значит списываем 200
4450120Нужно 50 но на счету 20 значит списываем 20
5570010Нужно списать 700 но на счету 0 поэтому 0


SELECT @@VERSION
Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)


Объемы примерно 10000 клиентов, у каждого по 1-3 счета.
3 дек 14, 16:46    [16944829]     Ответить | Цитировать Сообщить модератору
 Re: Набор суммы для каждого счета.  [new]
Edkonst2008
Member

Откуда:
Сообщений: 503
Hamber,

Шаг 1. Набираете курсор на списание
Шаг 2. В курсоре обрабатываешь списания согласно начислениям по приоритету
Шаг 3. В курсоре меняются остатки на счетах после списания
Шаг 4. Закрываем курсор.
3 дек 14, 17:25    [16945064]     Ответить | Цитировать Сообщить модератору
 Re: Набор суммы для каждого счета.  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Edkonst2008
Hamber,

Шаг 1. Набираете курсор на списание
Шаг 2. В курсоре обрабатываешь списания согласно начислениям по приоритету
Шаг 3. В курсоре меняются остатки на счетах после списания
Шаг 4. Закрываем курсор.


В транзакцию не забудьте каждое списание обернуть.
3 дек 14, 17:32    [16945117]     Ответить | Цитировать Сообщить модератору
 Re: Набор суммы для каждого счета.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
имхо это перевернутая задача накопительного итога.
В один запрос решается.
3 дек 14, 18:05    [16945370]     Ответить | Цитировать Сообщить модератору
 Re: Набор суммы для каждого счета.  [new]
londinium
Member

Откуда: Киев
Сообщений: 1199
автор
имхо это перевернутая задача накопительного итога.
В один запрос решается.

Разве? А перенос остатка? (т.е. на 1-м счете 20 рублей, а мы списываем 100, тогда 80 надо передать на второй счет и т.д. Плюс клиент еще и в плюсе может оказаться :-))
3 дек 14, 20:32    [16946235]     Ответить | Цитировать Сообщить модератору
 Re: Набор суммы для каждого счета.  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
londinium
автор
имхо это перевернутая задача накопительного итога.
В один запрос решается.

Разве? А перенос остатка? (т.е. на 1-м счете 20 рублей, а мы списываем 100, тогда 80 надо передать на второй счет и т.д. Плюс клиент еще и в плюсе может оказаться :-))


На 2012 я бы еще извернулся в один проход запросом. Но на 2005 что-то идей нет. Только идти курсором и перебирать записи.
3 дек 14, 20:41    [16946268]     Ответить | Цитировать Сообщить модератору
 Re: Набор суммы для каждого счета.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
a_voronin
londinium
пропущено...

Разве? А перенос остатка? (т.е. на 1-м счете 20 рублей, а мы списываем 100, тогда 80 надо передать на второй счет и т.д. Плюс клиент еще и в плюсе может оказаться :-))


На 2012 я бы еще извернулся в один проход запросом. Но на 2005 что-то идей нет. Только идти курсором и перебирать записи.

да ладно, многие подобные "курсорные" вещи через рекурсивное cte делается, вот прямо в лоб

with cte as (
	select 
		lc.idclient, 
		priority = 0, 
		account = cast(null as varchar(20)),
		lc.summa,
		summatodo = cast(lc.summa as numeric(16, 2)),
		spis = cast(0 as numeric(16, 2))
	from @ListClient as lc
	union all
	select
		lab.idclient,
		lab.priority,
		account = lab.account,
		c.summa,
		summatodo = cast(c.summatodo - v.spis  as numeric(16, 2)),
		spis = cast(v.spis as numeric(16, 2))
	from cte as c
	join @ListAccBalance as lab on lab.idclient = c.idclient and lab.priority = c.priority + 1
	cross apply (
                select case when lab.summaost >= c.summatodo then c.summatodo else lab.summaost end
	) as v(spis)
)
select * 
from cte as c
where c.priority > 0
order by c.idclient, c.priority
3 дек 14, 20:57    [16946339]     Ответить | Цитировать Сообщить модератору
 Re: Набор суммы для каждого счета.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Shakill, естественно, в priority не должно быть дырок, иначе предварительно через row_number придется раздать порядок записям
3 дек 14, 21:02    [16946356]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить