ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите составить запрос: Распределение сумм оплат по поставкам
18 сообщений из 18, страница 1 из 1
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052210
Alexander_Yudakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander_Yudakov
Гость
Задачка
Кажется, элементарная, но решить не смог.
Пожалуйста, помогите. Заранее спасибо.

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

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

Код: plaintext
1.
2.
3.
Дата        Сумма
 01 . 09 . 2002     1000 
 03 . 09 . 2002      800 
 05 . 09 . 2002     1400


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

Код: plaintext
1.
2.
3.
Дата        Сумма
 01 . 09 . 2002      600 
 02 . 09 . 2002     1700 
 04 . 09 . 2002     1300


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

Код: plaintext
1.
2.
3.
4.
5.
6.
Дата           Дата         Зачтённая 
оплаты         поставки         сумма
 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
...
Рейтинг: 0 / 0
22.09.2002, 15:37:14
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052212
Фотография Дед Маздай
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дед Маздай
Участник
Откуда следует, что оплата от 02.09 раскидывается в пропорции 400 на 1-ю поставку, 800 - на 2-ю, 500 - на 3-ю (при том, что 2-я и 3-я поставки произошли позже даты платежа)? Вы говорите "распределение оплат по соответствующим поставкам". Я, честно говоря, никакого соответствия между оплатами и поставками здесь не вижу.
...
Рейтинг: 0 / 0
22.09.2002, 15:51:54
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052215
Alexander_Yudakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander_Yudakov
Гость
Уточняю некорректно сформулированную задачку:

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

При этом возможны как ситуации предоплаты (утром деньги — вечером стулья), так и ситауции постоплаты (утром стулья — вечером деньги). Кроме того, возможно отсутствие одного из двух, тогда результат запроса будет пустым.
...
Рейтинг: 0 / 0
22.09.2002, 16:12:35
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052218
Makc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Makc
Участник
А как у Вас решается вопрос соответствия оплат и поставок и номенклатурного справочника?
Т.е. что-то вроде ID товара я в Ваших таблицах не вижу...
Или это сводные таблицы по датам?
Уточните вопрос.
...
Рейтинг: 0 / 0
22.09.2002, 16:43:10
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052220
Alexander_Yudakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander_Yudakov
Гость
Спасибо за уточняющий вопрос.

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

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

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

Заранее спасибо.
...
Рейтинг: 0 / 0
22.09.2002, 16:57:07
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052221
Фотография Дед Маздай
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дед Маздай
Участник
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
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 решат это все за один запрос, ну я так себе гура, поэтому вот мой заведомо неоптимальный вариант:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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
...
Рейтинг: 0 / 0
22.09.2002, 17:41:16
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052225
Alexander_Yudakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander_Yudakov
Гость
Спасибо, Дед Маздай!
Вероятнее всего, придется на подобном варианте и остановиться.

Уважаемые читатели и писатели форума!
Если Вы хотя-бы смутно представляете, как можно решить задачку одним запросом, но Вам лень заниматься написанием текста,— подскажите хотя бы идею, — экспериментами сам займусь.
...
Рейтинг: 0 / 0
22.09.2002, 20:25:14
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052232
Nick_K
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nick_K
Гость
Александр!
Для того чтобы вам сделать запрос вам необходимо два ID:
ID клиента и ID документа на поставку товара, потом в зависимости от того что вы хотите получить в результате (какую форму) вам будет сделать это легко
...
Рейтинг: 0 / 0
22.09.2002, 23:33:06
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052249
Nick_K
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nick_K
Гость
Кошкинсон тебе во всяком случае придется эти ID сделать для идентификации а потом можешь цепляться за них или курсором или updat-ом
...
Рейтинг: 0 / 0
23.09.2002, 01:20:51
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052252
Alexander_Yudakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander_Yudakov
Гость
Спасибо, Nick_K, но…

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

2. Хотелось бы, если это вообще возможно, обойтись только запросом и не использовать программные циклы, курсоры и временные таблицы.
...
Рейтинг: 0 / 0
23.09.2002, 02:00:31
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052254
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Что-то намутил, не знаю только будет ли правильно работать при всех вариантах в Поставках и Оплатах. Если "выскачат" значения 0.00013 и 0.00023, то значит не правильно и придется использовать курсор.

Можно даже попробовать объединить все это в один запрос
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
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Дед Маздай
Да ладно, Вы иногда такую информацию и код выдаете в форум, что просто заглядение :-)
...
Рейтинг: 0 / 0
23.09.2002, 03:40:15
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052305
Фотография MiCe
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MiCe
Участник
2 glory...
если бы это были просто качели... ;))
а если на одну поставку было 5 оплат или на 5 поставок одна оплата?
...
Рейтинг: 0 / 0
23.09.2002, 12:04:10
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052364
Alexander_Yudakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander_Yudakov
Гость
Спасибо, Glory.

В реальной базе решение работает до неприличного быстро.
Но, как назло, вариантов «на одну поставку 5 оплат» и «на 5 поставок одна оплата» оказалось слишком много.
Придется использовать курсор… :(
...
Рейтинг: 0 / 0
23.09.2002, 13:33:50
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052459
Фотография MiCe
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MiCe
Участник
на самом деле легче повесить тригер на таблицу ОПЛАТА и вести дополнительную таблицу уже в готовом виде....
...
Рейтинг: 0 / 0
23.09.2002, 17:55:36
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052463
Фотография MiCe
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MiCe
Участник
да и еще думаю метод г-на Glory можно использовать рекурсивно.....
но нужно подумать.... а сейчас лень..... ;))
разделять множества......
завтра обмыслю....
...
Рейтинг: 0 / 0
23.09.2002, 17:59:52
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052489
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Да, нашлось несколько неточностей. Получилось так

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
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б то кидайте в форум.
...
Рейтинг: 0 / 0
23.09.2002, 18:55:23
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052497
Фотография MiCe
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MiCe
Участник
снимаю шляпу....
оч наглядно... можно и без временных таблиц....
...
Рейтинг: 0 / 0
23.09.2002, 19:18:18
| Ответить | Цитировать | Написать  
Помогите составить запрос: Распределение сумм оплат по поставкам
    #32052523
Alexander_Yudakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander_Yudakov
Гость
Огромное спасибо, Glory! Задачка, кажется, решена.

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

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
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 и Дед Маздай, еще раз большое вам спасибо!
...
Рейтинг: 0 / 0
23.09.2002, 23:26:22
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите составить запрос: Распределение сумм оплат по поставкам
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=32052215&tid=1820135]:
 0ms
get settings:
 7ms
get forum list:
 8ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 52ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 189ms
total:  323ms
созд. / загр.: 323ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]