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

Откуда:
Сообщений: 327
Всем привет.
Раньше вроде бы такие задачи легко решал, но ,видимо, годы дают о себе знать :)
Нужно сумму разнести на строки. В строках есть как и положительное так и отрицательные суммы.
Результатом должны быть строки с рассчитанным количеством для каждой строки. Рассчитанное кол-во может быть меньше исходной суммы в строке - главное чтобы общая сумма по строкам не превышало заявленной разносимой суммы.
Есть еще один нюанс - в результат должны попасть только те платежи, чья сумма в периоде больше 0. Если же сумма по периоду меньше нуля - то она должна уменьшить сумму предыдущего периода или последующего (без разницы) с пересчитанным количеством в этих периодах.
Желательно все это сделать красиво - одним запросом :).
DECLARE @N_Sum FLOAT = 7.3  -- сумма для разнесения

;WITH t (ID,N_SUM,d_post_date,n_period) AS 
	(
		SELECT  1 ID,		-- PK
				2 N_SUM,	-- сумма
				CONVERT(SMALLDATETIME,'20171001') d_post_date, --  дата
				201710 n_period   -- период (для упрощения)
		UNION ALL
		SELECT 2 , 1 N_SUM, CONVERT(SMALLDATETIME,'20171002') d_post_date,201710 n_period
		UNION ALL
		SELECT 3 ,  3 N_SUM, CONVERT(SMALLDATETIME,'20171102') d_post_date,201711 n_period
		UNION ALL
		SELECT 5 ,  -4 N_SUM, CONVERT(SMALLDATETIME,'20171103') d_post_date,201711 n_period
		UNION ALL
		SELECT 4 , 5 N_SUM, CONVERT(SMALLDATETIME,'20171203') d_post_date,201712 n_period
		UNION ALL
		SELECT 6 LINK, 1 N_SUM, CONVERT(SMALLDATETIME,'20171204') d_post_date,201712 n_period
	
	)
	SELECT	t.*,
			CASE	WHEN t.sum_by_step < t.n_need AND t.sum_by_step <= sum_by_period  THEN t.N_SUM 
					WHEN t.sum_by_step < t.n_need AND t.sum_by_step > sum_by_period  THEN t.sum_period 
					WHEN t.sum_by_step >= t.n_need AND t.N_SUM >= t.n_need AND t.n_need <= t.sum_period THEN t.n_need
					WHEN t.sum_by_step >= t.n_need AND t.N_SUM >= t.n_need AND t.n_need > t.sum_period THEN t.sum_period
					WHEN t.sum_by_step >= t.n_need AND t.N_SUM < t.n_need  and t.N_SUM <= t.sum_period THEN  t.N_SUM
					WHEN t.sum_by_step >= t.n_need AND t.N_SUM < t.n_need  and t.N_SUM > t.sum_period THEN  t.sum_period
			END   n_quant
	FROM (
			SELECT	T.*,
					@N_Sum - ISNULL(LAG(t.sum_by_step) OVER (ORDER BY d_post_date,N_SUM DESC ,ID),0) n_need  -- необходимое кол-во
			FROM  (
				SELECT	*,
						SUM(N_SUM) OVER (ORDER BY d_post_date,N_SUM DESC ,ID)			sum_by_step, -- нарастающий итог
						SUM(N_SUM) OVER (ORDER BY n_period )							sum_by_period, -- нарастающий итог в пределах периода
						SUM(N_SUM) OVER (PARTITION BY n_period ORDER BY n_period)		sum_period -- сумма по периоду
				
				FROM T
				WHERE N_SUM <> 0
				) t	
			WHERE t.sum_period > 0	
		) t

Но мой вариант кривоват так как не правильно анализируется отрицательный остаток в периоде.
Спасибо.
12 дек 17, 16:59    [21028024]     Ответить | Цитировать Сообщить модератору
 Re: Разноска суммы. Нарастающий итог.  [new]
Владислав Колосов
Member

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

варианты расчета нарастающего итога есть в FAQ форума.
12 дек 17, 18:06    [21028343]     Ответить | Цитировать Сообщить модератору
 Re: Разноска суммы. Нарастающий итог.  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 327
Владислав Колосов,

Эта задача не на расчет , а на использование нарастающего итога.
12 дек 17, 18:31    [21028417]     Ответить | Цитировать Сообщить модератору
 Re: Разноска суммы. Нарастающий итог.  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 327
Cristiano_Rivaldo
Результатом должны быть строки с рассчитанным количеством для каждой строки. Рассчитанное кол-во может быть меньше исходной суммы в строке - главное чтобы общая сумма по строкам не превышало заявленной разносимой суммы.

Забыл уточнить.Строки с отрицательными суммами не должны попасть в результат.
12 дек 17, 18:58    [21028485]     Ответить | Цитировать Сообщить модератору
 Re: Разноска суммы. Нарастающий итог.  [new]
Владислав Колосов
Member

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

То есть Вы хотите, чтобы отрицательный остаток переносился в новый период и так далее до тех пор, пока не получите положительный остаток из суммы всех подряд идущих отрицательных остатков к последующему положительному?
12 дек 17, 19:11    [21028505]     Ответить | Цитировать Сообщить модератору
 Re: Разноска суммы. Нарастающий итог.  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 327
Владислав Колосов,

Отрицательный остаток - это уменьшение общей суммы последующего (или предыдущего) периода, которую нужно учитывать при определении необходимого кол-ва для разнесения. Например для примера, который я приводил один из вариантов может выглядеть так:
id n_quant
1   2
2   1
4   4    -- не 5, а 4  потому что за прошлый период сумма = -1
6   0.3
12 дек 17, 19:52    [21028571]     Ответить | Цитировать Сообщить модератору
 Re: Разноска суммы. Нарастающий итог.  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 327
Up
13 дек 17, 08:51    [21029345]     Ответить | Цитировать Сообщить модератору
 Re: Разноска суммы. Нарастающий итог.  [new]
Владислав Колосов
Member

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

непонятно почему
автор
4 4 -- не 5, а 4 потому что за прошлый период сумма = -1


Если сложить с предыдущим значением, получается 1.
13 дек 17, 11:21    [21029827]     Ответить | Цитировать Сообщить модератору
 Re: Разноска суммы. Нарастающий итог.  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 327
Владислав Колосов,

Попробую объяснить по другому.
Отрицательные стоки - это сторно. Фактически они влияют только на общую сумму по периоду.Нужно анализировать не только сумму предыдущей строки, но и сумму предыдущего периода в целом. Поэтому в примере отрицательный остаток - 1 за ноябрь "забирает" сумму = 1 у строк декабря.
13 дек 17, 11:58    [21030088]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить