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

Откуда: Москва
Сообщений: 311
В FAQ встречается много подобных задачек, которые реализованы либо для одной группы записей, либо с использованием курсора.

На больших объемах документов это работает достаточно медленно.

Пробовал это реализовать через UPDATE с использованием переменных:

UPDATE 
SET @param = ***, SummaSpis = @param

Но если ID в табличке неотсортированы, а добавлены хаотично, он неправильно их проапдейтит.

У меня задачка немного сложнее.

Есть табличка остатков назовем её Rest.

Rest
IDOstatok
110
25
37


В этой табличке для каждого ID хранится его остаток.

Есть табличка с проводками Doc (договора там могут быть добавлены хаотично неотсортированы по ID,OrderNum)

IDOrderNumSumSummaSpisSummaOst


ID - договор
OrderNum - порядок следования списания
Sum - сумма
SummaSpis - сумма которая реально будет списана (эти поля необходимо вычислить)
SummaOst - сумма остатка (эти поля необходимо вычислить)

Doc
IDOrderNumSumSummaSpisSummaOst
112
314
127
216
132


Т.е. Для первого счета у нас есть 3 списания, для остальных по одному.

В итоге табличка должна выглядеть следующим образом

Doc
IDOrderNumSumSummaSpisSummaOst
11228
31441
12771
21651
13225


Т.е. мы видимо что

счет 1, списание 1, сумма 2 рубля, 2 рубля будут списаны, остаток 8
счет 1, списание 2, сумма 7 руб, 7 рублей будут списаны, остаток 1
счет 1, списание 3, сумма 2 руб, 1 руб будет списан, долг 1 руб. т.к. Sum-SummaSpis<>0
счет 2, списание 1, сумма 6 руб, 5 руб списан, долг 1 руб т.к. Sum-SummaSpis<>0
счет 3, списание 1, сумма 2 руб, 2 руб списан, остаток 5 руб.


Как это реализовать на SQL?
15 ноя 11, 17:24    [11601347]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог с минусом  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
Hamber,
если правильно понял, то как то так:
заджойнить таблицу с проводками саму с собой, по уловию t1.ID=t2.ID and t1.OrderNum>=t2.OrderNum
заджойнить остатки (t3) по условию t3.ID = t1.ID
сгрупировать по t1.ID, t1.OrderNum, t3.Ostatok,t1.Sum
в селекте написать t1.ID, t1.OrderNum, t1.Sum, case when t3.Ostatok - Sum(t2.Sum)<0 then t1.Sum+t3.Ostatok-Sum(t2.Sum) else t1.Sum end SummaSpis, case when t3.Ostatok - Sum(t2.Sum)<0 then 0 else t3.Ostatok - Sum(t2.Sum)< end SummaOst
15 ноя 11, 18:36    [11601922]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог с минусом  [new]
Hamber
Member

Откуда: Москва
Сообщений: 311
Не совсем понял то что вы написали.
16 ноя 11, 09:25    [11603424]     Ответить | Цитировать Сообщить модератору
 Re: Нарастающий итог с минусом  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
Hamber,
Если в Rest начальный остаток и он связан с Doc по ID, а OrderNum порядок списания, то можно так:
DECLARE @Rest TABLE(ID int, Ostatok int)

INSERT @Rest VALUES (1,10), (2,5), (3,7)

DECLARE @Doc TABLE (ID int, OrderNum int, Sum int, SummaSpis int, SummaOst int)

INSERT @Doc (ID, OrderNum, Sum) VALUES 
	(1,1,2)
	,(3,1,4)
	,(1,2,7)
	,(2,1,6)
	,(1,3,2)
-- вариант 1
; WITH cte AS (
	SELECT
		a.ID
		,a.OrderNum
		,a.Sum
		,CASE
			WHEN b.Ostatok - a.Sum<0 then a.Sum-b.Ostatok
			ELSE a.Sum
		END SummaSpis
		,CASE
			WHEN b.Ostatok - a.Sum<0 THEN 0
			ELSE b.Ostatok - a.Sum
		END SummaOst 
	FROM @Doc a
		JOIN @Rest b ON a.ID = b.ID
	WHERE a.OrderNum = 1
	UNION ALL SELECT
		a.ID
		,a.OrderNum
		,a.Sum
		,CASE
			WHEN b.SummaOst - a.Sum<0 then a.Sum-b.SummaOst
			ELSE a.Sum
		END SummaSpis
		,CASE
			WHEN b.SummaOst - a.Sum<0 THEN 0
			ELSE b.SummaOst - a.Sum
		END SummaOst 
	FROM @Doc a
		JOIN cte b ON a.ID = b.ID AND a.OrderNum = b.OrderNum+1
)
SELECT * FROM cte ORDER BY 1,2
-- вариант 2
SELECT
	t1.ID
	,t1.OrderNum
	,t1.Sum
	,CASE
		WHEN t3.Ostatok - Sum(t2.Sum)<0 then t1.Sum+t3.Ostatok-Sum(t2.Sum)
		ELSE t1.Sum
	END SummaSpis
	,CASE
		WHEN t3.Ostatok - Sum(t2.Sum)<0 THEN 0
		ELSE t3.Ostatok - Sum(t2.Sum)
	END SummaOst 
FROM @Doc t1
	JOIN @Doc t2 ON t1.ID = t2.ID AND t1.OrderNum >= t2.OrderNum
	JOIN @Rest t3 ON t1.ID = t3.ID
GROUP BY
	t1.ID
	,t1.OrderNum
	,t3.Ostatok
	,t1.Sum
ORDER BY 1,2

ещё не понятно:
1) как из исходных данных получилась итоговая табличка
2) как итоговая таблица связана с описанием расчета что под ней
3) если списание больше остатка, что выводить, 0 или -Х ?

ps: "На больших объемах документов это работает достаточно медленно." - это сколько ?
16 ноя 11, 15:13    [11606668]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить