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

Откуда: Самара
Сообщений: 27
Задачка
Кажется, элементарная, но решить не смог.
Пожалуйста, помогите. Заранее спасибо.

Условие
Есть две таблицы: список поставок и список оплат.

Поставки (Дата datetime, Сумма money)

Дата        Сумма

01.09.2002 1000
03.09.2002 800
05.09.2002 1400


Оплаты (Дата datetime, Сумма money)

Дата        Сумма

01.09.2002 600
02.09.2002 1700
04.09.2002 1300


Нужно
Составить запрос, который выдаст распределение оплат по соответствующим поставкам при условии, что первыми оплачиваются поставки, произведенные ранее.

Дата           Дата         Зачтённая 

оплаты поставки сумма
01.09.2002 01.09.2002 600
02.09.2002 01.09.2002 400
02.09.2002 03.09.2002 800
02.09.2002 05.09.2002 500
04.09.2002 05.09.2002 900
22 сен 02, 15:37    [56716]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Дед Маздай
Member

Откуда:
Сообщений: 655
Откуда следует, что оплата от 02.09 раскидывается в пропорции 400 на 1-ю поставку, 800 - на 2-ю, 500 - на 3-ю (при том, что 2-я и 3-я поставки произошли позже даты платежа)? Вы говорите "распределение оплат по соответствующим поставкам". Я, честно говоря, никакого соответствия между оплатами и поставками здесь не вижу.
22 сен 02, 15:51    [56720]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Alexander_Yudakov
Member

Откуда: Самара
Сообщений: 27
Уточняю некорректно сформулированную задачку:

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

При этом возможны как ситуации предоплаты (утром деньги — вечером стулья), так и ситауции постоплаты (утром стулья — вечером деньги). Кроме того, возможно отсутствие одного из двух, тогда результат запроса будет пустым.
22 сен 02, 16:12    [56723]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Makc
Member

Откуда:
Сообщений: 285
А как у Вас решается вопрос соответствия оплат и поставок и номенклатурного справочника?
Т.е. что-то вроде ID товара я в Ваших таблицах не вижу...
Или это сводные таблицы по датам?
Уточните вопрос.
22 сен 02, 16:43    [56727]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Alexander_Yudakov
Member

Откуда: Самара
Сообщений: 27
Спасибо за уточняющий вопрос.

Идентификаторы товаров и контрагентов и др., которые разумеется присутствуют в реальных таблицах, намеренно убраны с целью упрощения задачи.

Полагаю, что если удастся решить задачку в таком, наиболее простом виде, добавить распределение по объектам аналитического учета труда не составит.

Если это удобнее, можно считать представленные исходные таблицы сводными по датам.

Заранее спасибо.
22 сен 02, 16:57    [56729]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Дед Маздай
Member

Откуда:
Сообщений: 655
create table Supplies (id int identity, dat smalldatetime, amount smallmoney)

insert Supplies (dat, amount) values ('2002-09-01', 1000)
insert Supplies (dat, amount) values ('2002-09-03', 800)
insert Supplies (dat, amount) values ('2002-09-05', 1400)

create table Payments (id int identity, dat smalldatetime, amount smallmoney)
insert Payments (dat, amount) values ('2002-09-01', 600)
insert Payments (dat, amount) values ('2002-09-02', 1700)
insert Payments (dat, amount) values ('2002-09-04', 1300)


Наверняка гуры вроде SergSuper или Glory решат это все за один запрос, ну я так себе гура, поэтому вот мой заведомо неоптимальный вариант:

drop table #p

select * into #p from Payments
declare @p_id int, @p_amount smallmoney, @s_id int, @s_amount smallmoney
declare cur cursor for select id, amount from Supplies order by id
open cur
while (1 = 1) begin
fetch from cur into @s_id, @s_amount
if @@fetch_status <> 0 break
select top 1 @p_id = p1.id, @p_amount = sum(p2.amount) from #p p1
inner join #p p2 on p1.id >= p2.id
group by p1.id having sum(p2.amount) >= @s_amount
order by p1.id
select @s_id as 'ID поставки', @s_amount as 'Сумма поставки', id as 'ID платежа',
amount as 'Сумма платежа' from #p where id < @p_id
union all
select @s_id, @s_amount, id, @s_amount - @p_amount + amount from #p where id = @p_id
delete from #p where id < @p_id
update #p set amount = @p_amount - @s_amount where id = @p_id
end
close cur
deallocate cur
22 сен 02, 17:41    [56730]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Alexander_Yudakov
Member

Откуда: Самара
Сообщений: 27
Спасибо, Дед Маздай!
Вероятнее всего, придется на подобном варианте и остановиться.

Уважаемые читатели и писатели форума!
Если Вы хотя-бы смутно представляете, как можно решить задачку одним запросом, но Вам лень заниматься написанием текста,— подскажите хотя бы идею, — экспериментами сам займусь.
22 сен 02, 20:25    [56734]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Nick_K
Member

Откуда:
Сообщений: 12
Александр!
Для того чтобы вам сделать запрос вам необходимо два ID:
ID клиента и ID документа на поставку товара, потом в зависимости от того что вы хотите получить в результате (какую форму) вам будет сделать это легко
22 сен 02, 23:33    [56745]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Nick_K
Member

Откуда:
Сообщений: 12
Кошкинсон тебе во всяком случае придется эти ID сделать для идентификации а потом можешь цепляться за них или курсором или updat-ом
23 сен 02, 01:20    [56762]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Alexander_Yudakov
Member

Откуда: Самара
Сообщений: 27
Спасибо, Nick_K, но…

1. В приведенном примере для упрощения задачи, а вернее для уточнения ее предмета намеренно убраны упоминания об отдельных клиентах и документах. Условимся, что существует один единственный клиент, а в сутки возможна только одна поставка и одна оплата; таким образом даты поставок и оплат можно считать первичными ключами. Аналитику потом навернем — если удастся решить задачу принципиально.

2. Хотелось бы, если это вообще возможно, обойтись только запросом и не использовать программные циклы, курсоры и временные таблицы.
23 сен 02, 02:00    [56766]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Glory
Member

Откуда:
Сообщений: 104760
Что-то намутил, не знаю только будет ли правильно работать при всех вариантах в Поставках и Оплатах. Если "выскачат" значения 0.00013 и 0.00023, то значит не правильно и придется использовать курсор.

Можно даже попробовать объединить все это в один запрос

set nocount on
if object_id('tempdb..#Supplies') is not null drop table #Supplies
create table #Supplies (id int identity, dat smalldatetime, amount smallmoney)
insert #Supplies (dat, amount) values ('2002-09-01', 1000)
insert #Supplies (dat, amount) values ('2002-09-03', 800)
insert #Supplies (dat, amount) values ('2002-09-05', 1400)

if object_id('tempdb..#Payments') is not null drop table #Payments
create table #Payments (id int identity, dat smalldatetime, amount smallmoney)
--insert #Payments (dat, amount) values ('2002-08-05', 1000)

--insert #Payments (dat, amount) values ('2002-08-05', 300)

insert #Payments (dat, amount) values ('2002-09-01', 600)
insert #Payments (dat, amount) values ('2002-09-02', 1700)
insert #Payments (dat, amount) values ('2002-09-04', 1300)
--insert #Payments (dat, amount) values ('2002-09-05', 300)



if object_id('tempdb..#t1') is not null drop table #t1
select a.id, a.sum1, (a.tot_sum1 - a.sum1) as tot_sum1, b.dat into #t1
from
(select a.id, max(a.amount) as sum1, sum(isnull(b.amount, 0.00)) + max(a.amount) as tot_sum1
from #supplies a
left outer join #supplies b on b.id < a.id
group by a.id
) as a
inner join #supplies b on b.id = a.id

if object_id('tempdb..#t2') is not null drop table #t2
select a.id, a.sum2, (a.tot_sum2 - a.sum2) as tot_sum2, b.dat into #t2 from
(select a.id, max(a.amount) as sum2, sum(isnull(b.amount, 0.00))+max(a.amount) as tot_sum2
from #payments a
left outer join #payments b on b.id < a.id
group by a.id
) as a
inner join #payments b on b.id = a.id


select * from
(select a.id as id_1, a.sum1, a.tot_sum1, b.id as id_2, b.sum2, b.tot_sum2,
case when a.tot_sum1 > b.tot_sum2
then case when (b.sum2 - (a.tot_sum1 - b.tot_sum2)) > a.sum1
then a.sum1
when (b.sum2 - (a.tot_sum1 - b.tot_sum2)) < a.sum1
then b.sum2 - (a.tot_sum1 - b.tot_sum2)
else 0.00013
end

when a.tot_sum1 < b.tot_sum2
then case when ((b.tot_sum2 + b.sum2) - a.tot_sum1) > a.sum1
then (a.sum1 - (b.tot_sum2 - a.tot_sum1))
when ((b.tot_sum2 + b.sum2) - a.tot_sum1) < a.sum1
then a.sum1 - b.tot_sum2 - b.sum2
else 0.00023

end

else case when a.sum1 > b.sum2
then b.sum2
when a.sum1 <= b.sum2
then a.sum1
end
end x
from #t1 a
cross join #t2 b
) as a
where x > 0
order by a.id_1, a.id_2


2Дед Маздай
Да ладно, Вы иногда такую информацию и код выдаете в форум, что просто заглядение :-)
23 сен 02, 03:40    [56768]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
MiCe
Member

Откуда: RUSSIA STAVROPOL Pyatigorsk
Сообщений: 1996
2 glory...
если бы это были просто качели... ;))
а если на одну поставку было 5 оплат или на 5 поставок одна оплата?
23 сен 02, 12:04    [56835]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Alexander_Yudakov
Member

Откуда: Самара
Сообщений: 27
Спасибо, Glory.

В реальной базе решение работает до неприличного быстро.
Но, как назло, вариантов «на одну поставку 5 оплат» и «на 5 поставок одна оплата» оказалось слишком много.
Придется использовать курсор… :(
23 сен 02, 13:33    [56914]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
MiCe
Member

Откуда: RUSSIA STAVROPOL Pyatigorsk
Сообщений: 1996
на самом деле легче повесить тригер на таблицу ОПЛАТА и вести дополнительную таблицу уже в готовом виде....
23 сен 02, 17:55    [57025]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
MiCe
Member

Откуда: RUSSIA STAVROPOL Pyatigorsk
Сообщений: 1996
да и еще думаю метод г-на Glory можно использовать рекурсивно.....
но нужно подумать.... а сейчас лень..... ;))
разделять множества......
завтра обмыслю....
23 сен 02, 17:59    [57030]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Glory
Member

Откуда:
Сообщений: 104760
Да, нашлось несколько неточностей. Получилось так


set nocount on
if object_id('tempdb..#Supplies') is not null drop table #Supplies
create table #Supplies (id int identity, dat smalldatetime, amount smallmoney)
insert #Supplies (dat, amount) values ('2002-09-01', 1000)
insert #Supplies (dat, amount) values ('2002-09-03', 800)
insert #Supplies (dat, amount) values ('2002-09-05', 1400)

if object_id('tempdb..#Payments') is not null drop table #Payments
create table #Payments (id int identity, dat smalldatetime, amount smallmoney)
--insert #Payments (dat, amount) values ('2002-08-05', 1000)

--insert #Payments (dat, amount) values ('2002-08-05', 300)

insert #Payments (dat, amount) values ('2002-09-01', 600)
--insert #Payments (dat, amount) values ('2002-09-02', 900)

insert #Payments (dat, amount) values ('2002-09-02', 1700)
insert #Payments (dat, amount) values ('2002-09-04', 1300)
--insert #Payments (dat, amount) values ('2002-09-05', 100)



if object_id('tempdb..#t1') is not null drop table #t1
select a.id, a.sum1, (a.tot_sum1 - a.sum1) as tot_sum1, b.dat into #t1
from
(select a.id, max(a.amount) as sum1, sum(isnull(b.amount, 0.00)) + max(a.amount) as tot_sum1
from #supplies a
left outer join #supplies b on b.id < a.id
group by a.id
) as a
inner join #supplies b on b.id = a.id

if object_id('tempdb..#t2') is not null drop table #t2
select a.id, a.sum2, (a.tot_sum2 - a.sum2) as tot_sum2, b.dat into #t2 from
(select a.id, max(a.amount) as sum2, sum(isnull(b.amount, 0.00))+max(a.amount) as tot_sum2
from #payments a
left outer join #payments b on b.id < a.id
group by a.id
) as a
inner join #payments b on b.id = a.id


select * from
(select a.id as id_1, a.sum1, a.tot_sum1, b.id as id_2, b.sum2, b.tot_sum2,
case when a.tot_sum1 > b.tot_sum2
then case when (b.sum2 - (a.tot_sum1 - b.tot_sum2)) > a.sum1
then a.sum1
when (b.sum2 - (a.tot_sum1 - b.tot_sum2)) <= a.sum1
then b.sum2 - (a.tot_sum1 - b.tot_sum2)
end

when a.tot_sum1 < b.tot_sum2
then case when ((b.tot_sum2 + b.sum2) - a.tot_sum1) >= a.sum1
then (a.sum1 - (b.tot_sum2 - a.tot_sum1))
when ((b.tot_sum2 + b.sum2) - a.tot_sum1) < a.sum1
then b.sum2

end

else case when a.sum1 > b.sum2
then b.sum2
when a.sum1 <= b.sum2
then a.sum1
end
end x
from #t1 a
cross join #t2 b
) as a
where x > 0
order by a.id_1, a.id_2


Перепробовал различные варианты (см. закомментированные строки). Не скажу, что все потому как запутался под конец. Но варианты с "перекосом" в ту и дуругую сторону были. Вроде бы как работает. Кстати и сам case получился логично/симметричным.
Если найдте "нерабочую" последовательность Supplies/Paymentsб то кидайте в форум.
23 сен 02, 18:55    [57058]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
MiCe
Member

Откуда: RUSSIA STAVROPOL Pyatigorsk
Сообщений: 1996
снимаю шляпу....
оч наглядно... можно и без временных таблиц....
23 сен 02, 19:18    [57067]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос: Распределение сумм оплат по поставкам  [new]
Alexander_Yudakov
Member

Откуда: Самара
Сообщений: 27
Огромное спасибо, Glory! Задачка, кажется, решена.

Пока был в офф-лайне, пришлось правда использовать более громоздкий вариант:

If Object_ID('#Supplies') is not null Drop table #Supplies

Create table #Supplies(Dat datetime primary key, Amount money)
Insert #Supplies values ('2002-09-01', 1000)
Insert #Supplies values ('2002-09-03', 800)
Insert #Supplies values ('2002-09-05', 1400)

If Object_ID('#Payments') is not null Drop table #Payments
Create table #Payments(Dat datetime primary key, Amount money)
--Insert #Payments values ('2002-08-05', 1000)

--Insert #Payments values ('2002-08-05', 300)

Insert #Payments values ('2002-09-01', 600)
--Insert #Payments values ('2002-09-02', 900)

Insert #Payments values ('2002-09-02', 1700)
Insert #Payments values ('2002-09-04', 1300)
--insert #Payments values ('2002-09-05', 100)


If Object_ID('#Total_Payments') is not null Drop table #Total_Payments
Select a.Dat, a.Amount, sum(b.Amount) Total
into #Total_Payments
from #Payments a
right join #Payments b
on b.Dat <= a.Dat
group by a.Dat, a.Amount

If Object_ID('#Total_Supplies') is not null Drop table #Total_Supplies
Select a.Dat, a.Amount, sum(b.Amount) Total
into #Total_Supplies
from #Supplies a
right join #Supplies b
on b.Dat <= a.Dat
group by a.Dat, a.Amount

If Object_ID('#Buffer') is not null Drop table #Buffer
Select
tp.Dat Payment_Date,
ts.Dat Supply_Date,
tp.Amount Payment_Amount,
tp.Total Payment_Total,
ts.Amount Supply_Amount,
ts.Total Supply_Total

into #Buffer
from #Total_Payments tp, #Total_Supplies ts

where tp.Total > ts.Total - ts.Amount
and tp.Total - tp.Amount < ts.Total

Select b.*,
(
(case when b.Supply_Total - b.Payment_Total > 0
then b.Payment_Total
else b.Supply_Total
end) -
IsNull
(
(
Select Max
(case when b1.Supply_Total - b1.Payment_Total > 0
then b1.Payment_Total
else b1.Supply_Total
end)

from #Buffer b1

where
(case when b1.Supply_Total - b1.Payment_Total > 0
then b1.Payment_Total
else b1.Supply_Total
end)
<
(case when b.Supply_Total - b.Payment_Total > 0
then b.Payment_Total
else b.Supply_Total
end)
), 0
)
) Closed

from #Buffer b
order by b.Payment_Date, b.Supply_Date


Glory и Дед Маздай, еще раз большое вам спасибо!
23 сен 02, 23:26    [57106]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить