Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Списание суммы  [new]
19042017
Guest
Здравствуйте !
Есть табличка @Tmp (отсортирована по ACode)
ACodeSumma
2000100
2000200
2000300
2000400
2002500
2012600
2012700

и некая сумма (2000), которую нужно списать, так чтобы в результате получилось следующее
ACodeSumma
20000
20000
20000
20000
20020
2012100
2012700

Написал следующий код, он выдаёт нужный результат, но хотелось бы обойтись без цикла.
Помогите пожалуйста
+
declare @Tmp table(ACode int, Summa money)
declare @Summa money = 2000

insert into @Tmp values(2000,100),(2000,200),(2000,300),(2000,400),(2002,500),(2012,600),(2012,700)


declare @Sum money

while @Summa > 0 begin
  select top 1 @Sum = Summa from @Tmp where Summa > 0 order by ACode,Summa

  update @Tmp
    set Summa = case when @Sum < @Summa then 0 else @Sum-@Summa end
  where Summa = @Sum
  
  select @Summa = case when @Summa < @Sum then 0 else @Summa-@Sum end
end

select * from @Tmp
19 апр 17, 11:21    [20413621]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
19042017
отсортирована по ACode
Это как?! Там же одно и то же значение!
19 апр 17, 11:23    [20413636]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
19042017
Guest
Там разные значения 2000,2002,2012
19 апр 17, 11:26    [20413648]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
SQL 2014
WITH T AS
(
 SELECT * FROM
 (
  VALUES
  (2000,100)
 ,(2000,200)
 ,(2000,300)
 ,(2000,400)
 ,(2002,500)
 ,(2012,600)
 ,(2012,700)
 )T(ACode,Summa)
)
SELECT
 ACode
,CASE
  WHEN SUM(Summa)OVER(ORDER BY ACode,Summa)<=2000
  THEN 0
  WHEN SUM(Summa)OVER(ORDER BY ACode,Summa)<=2000+Summa
  THEN SUM(Summa)OVER(ORDER BY ACode,Summa)-2000
  ELSE Summa
 END
FROM T
ORDER BY ACode,Summa;

Однако, упорядочение по ACode неоднозначно. Почему бы вам не признать это и не исправить?
19 апр 17, 11:58    [20413777]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
19042017
Guest
iap
Однако, упорядочение по ACode неоднозначно. Почему бы вам не признать это и не исправить?

Почему неоднозначно ?
19 апр 17, 12:00    [20413792]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
buven
Member

Откуда:
Сообщений: 792
19042017
iap
Однако, упорядочение по ACode неоднозначно. Почему бы вам не признать это и не исправить?

Почему неоднозначно ?


Какой результат вы будете ждать, при попытке списать 100, на вот таких данных
VALUES
  (2000,100)
 ,(2000,200)

?
19 апр 17, 12:08    [20413825]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
s_ustinov
Member

Откуда: Munchen, DE
Сообщений: 2266
iap
Однако, упорядочение по ACode неоднозначно. Почему бы вам не признать это и не исправить?

+1
Сделайте ID с IDENTITY (как уникальный / первичный ключ), и сортируйте по нему.
19 апр 17, 12:12    [20413851]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
19042017
Guest
buven
Какой результат вы будете ждать, при попытке списать 100, на вот таких данных
VALUES
  (2000,100)
 ,(2000,200)

?

Результат
ACodeSumma
20000
2000200

или
ACodeSumma
2000100
2000100

не имеет значения, главное что с 2000 кода списалась сумма 100, а с какого из двух без разницы
19 апр 17, 12:14    [20413861]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
19042017
Guest
Msg 102, Level 15, State 1, Line 20
Неправильный синтаксис около конструкции "order".

Это наверное потому что SQL Server 2008 R2 ?
19 апр 17, 12:20    [20413891]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
Cane Cat Fisher
Member

Откуда:
Сообщений: 1809
Ну попробуйте списать 100 с таких значений:

values
(2000,100),
(2000,100),
(2000,100)
19 апр 17, 12:48    [20414055]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
19042017
Msg 102, Level 15, State 1, Line 20
Неправильный синтаксис около конструкции "order".

Это наверное потому что SQL Server 2008 R2 ?
Я ж указал, что написал для SQL 2014 и моложе.
Для 2008-го всё значительно сложнее.
19 апр 17, 12:50    [20414061]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
aleks2
Guest
iap
19042017
пропущено...

Это наверное потому что SQL Server 2008 R2 ?
Я ж указал, что написал для SQL 2014 и моложе.
Для 2008-го всё значительно сложнее.

Да ладно те, самосоединение с группировкой - это сложнее?
19 апр 17, 13:02    [20414124]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
aleks2
iap
пропущено...
Я ж указал, что написал для SQL 2014 и моложе.
Для 2008-го всё значительно сложнее.

Да ладно те, самосоединение с группировкой - это сложнее?
Сложнее. Разве нет?
19 апр 17, 13:14    [20414211]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
aleks2
Guest
iap
aleks2
пропущено...

Да ладно те, самосоединение с группировкой - это сложнее?
Сложнее. Разве нет?


Нет. Даже проще.

set nocount on;

-- как бы данные
declare @t table(ACode int, Summa int);

insert @t VALUES(2000,100);
insert @t VALUES(2000,200);
insert @t VALUES(2000,300);
insert @t VALUES(2000,400);
insert @t VALUES(2002,500);
insert @t VALUES(2012,600);
insert @t VALUES(2012,700);


-- рабочая таблица
declare @tt table(ACode int, Summa int, n int identity primary key );

-- копируем данные для списания...
insert @tt select * from @t order by ACode;

declare @Summa int, @x int; 

-- сумма списания
set @summa = 2000;

update @tt set @x = Summa
             , Summa = case when Summa < @Summa then 0 else Summa - @Summa end
			 , @Summa = case when @x < @Summa then @Summa - @x else 0 end
;


select * from @tt;

select Остаток = @Summa;
19 апр 17, 13:45    [20414414]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
19042017
Guest
Ваш код работает неверно. Получается вот такой результат
ACodeSumma
20000
20000
20000
20000
20020
2012600
2012700
19 апр 17, 15:01    [20414985]     Ответить | Цитировать Сообщить модератору
 Re: Списание суммы  [new]
aleks2
Guest
19042017
Ваш код работает неверно. Получается вот такой результат
ACodeSumma
20000
20000
20000
20000
20020
2012600
2012700


Клиент недоволен?
Ну дык, допили.
19 апр 17, 15:40    [20415285]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить