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

Откуда: МИНСК
Сообщений: 1184
Добрый день всем.
SQL 2012

Надо разбить 2 суммы бонуса (план и факт) пропорцианально другой сумме по документам

задача вроде станадратная
https://www.sql.ru/forum/1004370/polnoe-proporcionirovanie-summy-po-strokam-s-okrugleniem
оракловский вариант - но там захардокдено последняя строка

В идеале чтобы в последней строке добивалась дельта - (но не обязательно )
я могу и следующим update добить дельту.

Поля счетчика (1,2,3,4) нет в таблице (хотя его можно добавить )- Row_number() - думаю по
поможет здесь


че то гуглом не нарыл примеров хороших на T-sql - хотя уверен 100% что есть (можно англоязычных)

Единственный тонкий момент у меня
это разрядность - сумма бонуса у меня скажем 100$ а разбить надо на 10000 доков
(пока забил 2 знака после запятой )

Сообщение было отредактировано: 6 фев 20, 19:07
6 фев 20, 19:07    [22075080]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4288
Гулин Федор,

Что-то вроде такой конструкции.
Давайте тестовые данные, для конкретики.

		  d4.[A] = ISNULL(d4.[B]*d3.[A] / NULLIF(SUM(d4.[B]) over (PARTITION BY d4.C), 0), 0)
7 фев 20, 08:18    [22075283]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
a_voronin, так можно на ноль поделить Картинка с другого сайта.
7 фев 20, 10:21    [22075341]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30415
nullin
a_voronin, так можно на ноль поделить
У него же NULLIF
7 фев 20, 10:25    [22075345]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4288
nullin
a_voronin, так можно на ноль поделить Картинка с другого сайта.


Невнимательны вы, Батенька. Небось пишите

IIF(КилометровоеВыражение = 0, 0, ДвухКилометровоеВыражение/КилометровоеВыражение)


Сообщение было отредактировано: 7 фев 20, 10:34
7 фев 20, 10:33    [22075355]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
alexeyvg, а да, действительно Картинка с другого сайта.
ТСу поможет что-то типа такого:
iif(id = last_value(id) over(partition by schet_factura order by amount, id rows between current row and unbounded following)
      and sum(distributed_nds) over(partition by schet_factura) > 0
  , nds - sum(distributed_nds) over(partition by schet_factura)
  , 0)

Где выделено, нужно поставить что-то своё.
Только надо знать, стоит ли, ндс распределять если sum(amount) = 0
fiddle

Сообщение было отредактировано: 7 фев 20, 10:46
7 фев 20, 10:41    [22075361]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
a_voronin, не, километровое выражение в cross apply обернул

Сообщение было отредактировано: 7 фев 20, 10:49
7 фев 20, 10:49    [22075368]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
entrypoint
Member

Откуда:
Сообщений: 227
Гулин Федор,
+
-- Тестовые данные
-- --  Суммы по документам
WITH a AS (
    SELECT 1 AS [Ид Документа], 100.51 AS [План], 151.61 [Факт] UNION ALL 
    SELECT 1, 110.52456, 161.62123 UNION ALL 
    SELECT 1, 120.53127, 171.63456 UNION ALL 
    SELECT 1, 130.54678, 181.64789 UNION ALL 
    SELECT 2, 200.51567, 251.61987 UNION ALL 
    SELECT 2, 210.52112, 261.62654 UNION ALL 
    SELECT 2, 220.53997, 271.63345 UNION ALL 
    SELECT 2, 230.54456, 281.64345
), 
-- -- Распределяемые суммы
b AS ( 
    SELECT 1 AS [Ид Документа], 217.45784567 AS [Распределяемая сумма] UNION ALL 
    SELECT 2, 315.78473456
), 
-- Решение
c AS (  
SELECT 
       a.[Ид Документа]
     , a.[План]
     , a.[Факт]
	-- Приведением к MONEY уменьшил точность для наглядности
     , CONVERT(MONEY, b.[Распределяемая сумма] * a.[План]/SUM(a.[План]) OVER(PARTITION BY a.[Ид Документа])) AS [Распределенная Сумма План]
     -- Приведением к MONEY уменьшил точность для наглядности
     , CONVERT(MONEY, b.[Распределяемая сумма] * a.[Факт]/SUM(a.[Факт]) OVER(PARTITION BY a.[Ид Документа])) AS [Распределенная Сумма Факт]
     , b.[Распределяемая сумма]
FROM 
     a
     INNER JOIN b ON b.[Ид Документа] = a.[Ид Документа]), 
d AS (	 
SELECT 
       c.[Ид Документа]
     , c.[План]
     , c.[Факт]
     , c.[Распределенная Сумма План]
     , c.[Распределенная Сумма Факт]
     , c.[Распределяемая сумма]
     , SUM(c.[Распределенная Сумма План]) OVER(PARTITION BY c.[Ид Документа] ORDER BY (SELECT 1) 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Накопленная распределенная Сумма План]
     , SUM(c.[Распределенная Сумма Факт]) OVER(PARTITION BY c.[Ид Документа] ORDER BY (SELECT 1) 
		  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Накопленная распределенная Сумма Факт]
FROM 
     c
	)  
-- -- Финал
SELECT 
       d.[Ид Документа]
     , d.[План]
     , IIF(MAX(d.[Накопленная распределенная Сумма План]) OVER(PARTITION BY d.[Ид Документа]) = d.[Накопленная распределенная Сумма План],
			 d.[Распределенная Сумма План]
			 - d.[Накопленная распределенная Сумма План]
			 + d.[Распределяемая сумма], 
			 d.[Распределенная Сумма План]) AS [Распределенная Сумма План] 
     , d.[Факт]
     , IIF(MAX(d.[Накопленная распределенная Сумма Факт]) OVER(PARTITION BY d.[Ид Документа]) = d.[Накопленная распределенная Сумма Факт],
			 d.[Распределенная Сумма Факт]
			 - d.[Накопленная распределенная Сумма Факт]
			 + d.[Распределяемая сумма], 
			 d.[Распределенная Сумма Факт]) AS [Распределенная Сумма Факт]
FROM 
     d 
7 фев 20, 13:25    [22075565]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
a_voronin, а вот и делящий на ноль появился
7 фев 20, 13:46    [22075579]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4288
nullin
a_voronin, а вот и делящий на ноль появился


Это вы заключили лишь оттого, что сумма в знаменателе стоит?
7 фев 20, 17:18    [22075703]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
a_voronin, это про entrypoint
7 фев 20, 18:03    [22075739]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4288
nullin
a_voronin, это про entrypoint


А по вашему нет других способов предотвратить деление на 0. Например, иметь констрейнт, что поле > 0.

+
CONVERT(MONEY, b.[Распределяемая сумма] * a.[Факт]/SUM(a.[Факт]) OVER(PARTITION BY a.[Ид Документа])) AS [Распределенная Сумма Факт]
7 фев 20, 18:21    [22075744]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
a_voronin, нет смысла обсуждать констрейнты, т.к. в данном случае это может быть уже уровень бизнес-логики, например в срезе по ключевым датам [факт] может быть и ноль (особенно на такое понимание подталкивает то, что есть [план]), плюс ещё один фактор далее.
В примере CTE, но, да - это условность, а автор так и не удосужился DDL представить.Картинка с другого сайта.

Кстати эта тема не только на Oracle всплывала, помню и здесь за последние полгода - год, что-то такое было, там ещё, помнится Kopelly помог и кажется его вариант отличался от всех представленных на текущий момент.

Сообщение было отредактировано: 7 фев 20, 19:07
7 фев 20, 19:06    [22075762]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
nullin, Вот нашел тему, все-таки немного другое там было задание, но у ТС как раз возникла проблема, в том, что общее - убрать ошибку округления.
В теме на Oracle как-то этот момент захардкодили, умельцы Картинка с другого сайта.

Насколько важно наименовать темы в соответствии с решаемой проблемой
Помогите с запросом

Сообщение было отредактировано: 7 фев 20, 19:20
7 фев 20, 19:19    [22075773]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4288
nullin
но у ТС как раз возникла проблема, в том, что общее - убрать ошибку округления.


Я вывел для себя такой хак, для борьбы с ошибками округления.

0.000001 * SUM(CAST((1000000.0 * A * B) / C AS DECIMAL(38,14))
10 фев 20, 07:59    [22076502]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
a_voronin, выглядит странно, и мне кажется тут есть проблема: в запросе данной темы round(), значит если 500(мл) на 3 поделить, и округлить, то получится набор 166.67, 166.67, 166.67 -> 166.66 - ошибка в минус идет. Вот, если было бы floor().
10 фев 20, 11:20    [22076599]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4288
nullin
a_voronin, выглядит странно, и мне кажется тут есть проблема: в запросе данной темы round(), значит если 500(мл) на 3 поделить, и округлить, то получится набор 166.67, 166.67, 166.67 -> 166.66 - ошибка в минус идет. Вот, если было бы floor().


CAST(X AS DECIMAL(18,2)) вам сделает FLOOR
10 фев 20, 13:42    [22076779]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
a_voronin, наверно не совсем понятно выразился, в примере не floor, а round, так что так не прокатит, и способ этот на мой взгляд сомнителен.
10 фев 20, 17:34    [22077005]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4288
nullin,

Вот такой хак прокатит FLOOR(X * 100) * 0.01

SELECT CAST(X AS DECIMAL(10,2)), ROUND(X, 2), FLOOR(X * 100) * 0.01
FROM (VALUES (1.001), (1.4999), (1.4899), (1.4499), (1.4450), (1.44499), (1.5000), (1.50001), (1.9999)) t (X)
10 фев 20, 18:43    [22077088]     Ответить | Цитировать Сообщить модератору
 Re: разбить суммы пропорцианально другой сумме по документам  [new]
nullin
Member

Откуда: pullin
Сообщений: 112
a_voronin, там во какая штука есть:
round(55.7657657, 2, 1) = 55.76

По умолчанию вместо единицы - ноль. Если поставить что-либо отличное от нуля, то будет усекать.
В итоге поправка для распределения 500 на 3, будет +0.02, а не -0.01 Картинка с другого сайта.

Сообщение было отредактировано: 10 фев 20, 21:23
10 фев 20, 21:21    [22077204]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить