знатокам запросов с нарастающим итогом

вопрощающий

Дата: 08.09.2005 17:16:11

Предположим для простоты, что есть две таблицы, Payments и Invoices, не связанные между собой ключами.

PaymID	PaymDate	PaymAmount
1	01/06/05	$ 20000
2	01/07/05	$ 10000
3	01/08/05	$ 15000
4	01/09/05	$ 30000

InvID	InvDue	InvAmount
1	25/05/05	$ 15000
2	25/05/05	$ 4000
3	25/05/05	$ 6000
4	25/05/05	$ 15000
5	25/05/05	$ 25000
6	25/05/05	$ 15000
7	25/05/05	$ 10000

Вопрос: можно ли каким-либо хитрым образом получить запрос, показывающий только неоплаченные инвойсы (без третьей таблицы, "размещающей" платежи по инвойсам (либо через вспомогательный запрос, выполняющий функцию такой таблицы автоматически размещающий платеж по ближайшим инвойсам)? Т.е. в итоге должно получиться (если не ошибся в цифрах):

InvID	InvAmount	InvBalance
6	15000	5000
7	10000	10000

вдогонку

Дата: 08.09.2005 17:21:09

Даты инвойсов нужно было для правдоподобия разные указать, хотя это здесь непринципиально.

Владимир Саныч

Дата: 08.09.2005 17:24:12

А по какой формуле получен этот результат? Какая строка к какой относится?

Тяп-ляп

Дата: 08.09.2005 17:26:06

Дай определение оплаченности/неоплаченности инвойса. В нем будет ответ

Iskander68

Дата: 08.09.2005 17:46:34

Автор: завтра растолкую.

--
Regards
Alexander Artamonov

"Тяп-ляп" <nospam@sql.ru> сообщил/сообщила в новостях следующее:
news:1860423@sql.ru...
Дай определение оплаченности/неоплаченности инвойса. В нем будет ответ
Тема Ответить

Posted via ActualForum NNTP Server 1.3

(c)VIG

Дата: 08.09.2005 18:00:34

select a.id,iif( InvAmount+PrevAmount >v ,InvAmount+PrevAmount-v, InvAmount)
from
(select t.InvID,
  t.InvAmount,nz(t1.InvAmount,0)  PrevAmount,
 (select sum( PaymAmount) from Payments) v
from  Invoices t
left join Invoices t1 on t.InvID>t1.InvID
group by t.InvID,t.InvAmount
) a
where InvAmount+PrevAmount >v

(c)VIG

Дата: 08.09.2005 20:56:03

Маленькая поправка

select a.id,iif( PrevAmount <v ,InvAmount+PrevAmount-v, InvAmount)
from
(select t.InvID,
  t.InvAmount,nz(t1.InvAmount,0)  PrevAmount,
 (select sum( PaymAmount) from Payments) v
from  Invoices t
left join Invoices t1 on t.InvID>t1.InvID
group by t.InvID,t.InvAmount
) a
where InvAmount+PrevAmount >v

Iskander68

Дата: 09.09.2005 11:20:45

Заработал в таком виде:

SELECT a.InvID, IIf(PrevAmount<v,InvAmount+PrevAmount-v,InvAmount) AS
Balance
FROM (select t.InvID, t.InvAmount,nz(t1.InvAmount,0) as PrevAmount,
(select sum(PaymAmount) from Payments) as v
from Invoices t
left join Invoices t1 on t.InvID>t1.InvID
group by t.InvID, t.InvAmount, nz(t1.InvAmount,0)
) AS a
WHERE [InvAmount]+[PrevAmount]>[v];

Но ничего не показал.

--
Regards
Alexander Artamonov

Posted via ActualForum NNTP Server 1.3

Iskander68

Дата: 09.09.2005 11:29:08

Результат получен не по формуле, а по принципу:
берем первый по дате оплаты инвойс, вычитаем его сумму из первого платежа.
Если инвойс не покрыт, берем следующий платеж. В противном случае берем
следующий инвойс, вычитаем его сумму из остатка платежа - или из
следующего, если предыдущий исчерпан и т.д.

Т.е. обычно составляется таблица типа:

InvID	PaymID	Allocated
1	1	$ 15000
2	1	$ 4000
3	1	$ 1000
3	2	$ 5000
4	2	$ 5000
4	3	$ 10000
5	3	$ 5000
5	4	$ 20000
6	4	$ 10000

--
Regards
Alexander Artamonov

"Владимир Саныч" <nospam@sql.ru> сообщил/сообщила в новостях следующее:
news:1860414@sql.ru...
А по какой формуле получен этот результат? Какая строка к какой относится?
Тема Ответить

Posted via ActualForum NNTP Server 1.3

Pafluntiy

Дата: 09.09.2005 16:13:44

я мало пока понимаю в языке, но по логике я бы это понял так:

1 запрос - тупая сумма по всем оплатам

как-то надо получить запрос с нарастающим итогом в виде, где 123 - даты инвойсов по порядку:
Дата__|Сумма ______|нараст. Сумма
1_____|10000_______|10000________
2_____|5000________|15000________
3_____|3000________|18000________

Затем просто из этой таблицы получить список инвойсов с нарастающей суммай> суммы оплаченной. это и будут неоплаченные инвойсы или наоборот - это будут оплаченные