Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Построчный декремент на значение поля?  [new]
skoop
Guest
Уважаемые, подскажите пожалуйста, с какой стороны подойти к решению такой проблемы: есть список товаров на разных складах (у каждого склада свой уровень приоритета), нужно получить определённое количество предметов с этих складов, при том одно наименование предмета может быть получено как целиком из одного склада (если количество достаточно), так и из нескольких складов (в соответствии с приоритетом склада). На скриншоте примерно то, что хотелось бы получить:
Картинка с другого сайта.
Предмета А требуется 5 штук и он полностью берётся со склада S1 комната 1W1, а предмет B берётся со складов S1-1W1, S2-2W2, S3-3W2 и остаток из S4-4W1 (или S4-4W2, тут не принципиально т.к. приоритет равный). Сам побороть проблему не могу, т.к. не понимаю, с какой стороны тут браться... Так что за любую помощь буду очень благодарен.

Если требуется заготовка на поэкспериментировать, то вот:

declare @AvailableStock table (
  ItemCode varchar(20),
  Store varchar(2),
  Room varchar(3),
  StoreRank int,
  Quantity int
);

insert into @AvailableStock values ('A', 'S1', '1W1', 1, 10);
insert into @AvailableStock values ('A', 'S1', '1W2', 2, 10);
insert into @AvailableStock values ('A', 'S2', '2W1', 10, 10);
insert into @AvailableStock values ('A', 'S3', '3W1', 20, 10);
insert into @AvailableStock values ('A', 'S3', '3W2', 20, 10);

insert into @AvailableStock values ('B', 'S1', '1W1', 1, 2);
insert into @AvailableStock values ('B', 'S2', '2W1', 10, 3);
insert into @AvailableStock values ('B', 'S3', '3W2', 20, 1);
insert into @AvailableStock values ('B', 'S4', '4W1', 30, 2);
insert into @AvailableStock values ('B', 'S4', '4W2', 30, 2);


declare @NeedStock table (
  ItemCode varchar(20),
  Quantity int
);
insert into @NeedStock values ('A', 5);
insert into @NeedStock values ('B', 7);

select Avail.*,Need.Quantity as NeedQuantity from @AvailableStock Avail
join @NeedStock Need on Avail.ItemCode=Need.ItemCode
order by ItemCode, StoreRank;
30 ноя 16, 17:03    [19952454]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
declare @AvailableStock table (
  ItemCode varchar(20),
  Store varchar(2),
  Room varchar(3),
  StoreRank int,
  Quantity int
);

insert into @AvailableStock values ('A', 'S1', '1W1', 1, 10);
insert into @AvailableStock values ('A', 'S1', '1W2', 2, 10);
insert into @AvailableStock values ('A', 'S2', '2W1', 10, 10);
insert into @AvailableStock values ('A', 'S3', '3W1', 20, 10);
insert into @AvailableStock values ('A', 'S3', '3W2', 20, 10);

insert into @AvailableStock values ('B', 'S1', '1W1', 1, 2);
insert into @AvailableStock values ('B', 'S2', '2W1', 10, 3);
insert into @AvailableStock values ('B', 'S3', '3W2', 20, 1);
insert into @AvailableStock values ('B', 'S4', '4W1', 30, 2);
insert into @AvailableStock values ('B', 'S4', '4W2', 30, 2);


declare @NeedStock table (
  ItemCode varchar(20),
  Quantity int
);
insert into @NeedStock values ('A', 5);
insert into @NeedStock values ('B', 7);

select *,
case when dif < 0 then Quantity when lag(dif) over(partition by itemcode order by storerank) is null then dif when lag(dif) over(partition by itemcode order by storerank) < 0 then abs(lag(dif) over(partition by itemcode order by storerank)) else 0 end as QtyToAllocate
from(
select Avail.*,Need.Quantity as NeedQuantity
,sum(avail.Quantity) over(partition by avail.itemcode order by storerank) - Need.Quantity as dif 
from @AvailableStock Avail
join @NeedStock Need on Avail.ItemCode=Need.ItemCode
) a
30 ноя 16, 18:26    [19952823]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
Остаток товара надо хранить по одной записи на единицу товара, тогда такого вопроса вообще не возникнет. Забирается через
delete top (@required_amount) output into @temp from table where article = @article 
30 ноя 16, 18:34    [19952855]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
skoop
Guest
А что такое lag() ? У меня SQL server ругается:
автор
Msg 195, Level 15, State 10, Line 30
'lag' is not a recognized built-in function name.
30 ноя 16, 18:36    [19952864]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
skoop
А что такое lag() ? У меня SQL server ругается:
автор
Msg 195, Level 15, State 10, Line 30
'lag' is not a recognized built-in function name.


что бы получить правильный ответ, стоит сначала прочитать правила форума и начинать писать с указания результата выполнения

select @@version
30 ноя 16, 18:37    [19952867]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
returns,
Guest
skoop,

сделай функцію
create function dbo.test(@ItemNeed varchar(20),@QtyNeed int)
returns @Result table	(
						  ItemCode varchar(20),
						  Store varchar(2),
						  Room varchar(3),
						  StoreRank int,
						  Quantity int
						)
begin
	while 1=1 begin
	
		insert into @Result

		select top 1
			a.ItemCode
			,a.Store
			,a.Room
			,a.StoreRank
			,Quantity	=case when isnull(b.S,0)+a.Quantity>@QtyNeed then @QtyNeed-isnull(b.S,0) else a.Quantity end

		from	AvailableStock a
		outer apply (select sum(r.Quantity) as S, max(r.StoreRank) as R from @Result r) b	

		where a.ItemCode=@ItemNeed and isnull(b.S,0)<@QtyNeed and isnull(b.R,0)<a.StoreRank	
		order by a.StoreRank  

		--
		if @@rowcount=0 break
	end
	--
	return
end

и
select *
from @NeedStock
outer apply dbo.test(ItemCode,Quantity)
30 ноя 16, 18:38    [19952870]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
returns,,


автор
сделай функцію
и умри от истощения
30 ноя 16, 18:40    [19952878]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
skoop
Guest
Верблюд
Остаток товара надо хранить по одной записи на единицу товара, тогда такого вопроса вообще не возникнет. Забирается через
delete top (@required_amount) output into @temp from table where article = @article 
Не позваидую такой базе данных... у меня и так записей сотни две тысяч... а если их ещё и каждой единицы по одной буде - вообще можно будет рехнуться...

автор
select @@version

У меня такая: Microsoft SQL Server 2008 R2 (RTM)

По поводу функции - спасибо за идею, сейчас постараюсь придумать что-то на её основе, но без функции :)
30 ноя 16, 18:57    [19952932]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
skoop
Не позваидую такой базе данных... у меня и так записей сотни две тысяч... а если их ещё и каждой единицы по одной буде - вообще можно будет рехнуться...


У нас сейчас так. Несколько десятков миллионов товаров на складе и проблем нет ;)
30 ноя 16, 19:11    [19952971]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
skoop
Guest
Верблюд
У нас сейчас так. Несколько десятков миллионов товаров на складе и проблем нет ;)
Ну, даже если так - мне не подходит, я лишь работабю с таблицами другой программы

skoop
По поводу функции - спасибо за идею, сейчас постараюсь придумать что-то на её основе, но без функции :)
То ли я тут что-то не допонял, то ли как-то по-другому делаю не так, но получается не совсем то, не подскажете, в чём лажаю?
declare @Result table	(
						  ItemCode varchar(20),
						  Store varchar(2),
						  Room varchar(3),
						  StoreRank int,
						  Quantity int
						);
declare @ItemNeed varchar(20);
declare @QtyNeed int;

while 1=1 begin

  set @ItemNeed = (select top 1 ItemNeed from @NeedStock);
  set @QtyNeed = (select top 1 QtyNeed from @NeedStock);
  delete top (1) from @NeedStock;

	while 1=1 begin



		insert into @Result

		select top 1
			a.ItemCode
			,a.Store
			,a.Room
			,a.StoreRank
			,Quantity	=case when isnull(b.S,0)+a.Quantity>@QtyNeed then @QtyNeed-isnull(b.S,0) else a.Quantity end

		from	@AvailableStock a
		outer apply (select sum(r.Quantity) as S, max(r.StoreRank) as R from @Result r) b	

		where a.ItemCode=@ItemNeed and isnull(b.S,0)<@QtyNeed and isnull(b.R,0)<a.StoreRank	
		order by a.StoreRank  

		--
		if @@rowcount=0 break
	end;
  if (select COUNT(*) from @NeedStock)=0 break;
	
end;

select * from @Result;
30 ноя 16, 19:28    [19953031]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
skoop, банальная задача на расчет нарастающего итога. Жёвано-пережёвано.
1 дек 16, 11:26    [19954338]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Банальная, не банальная, главное - хвост! Я такие задачи редко решаю, мне интересно.
SELECT
  [ItemCode],
  [Store],
  [Room],
  [Has],
  [Need],
  [Leftover]
FROM (
  SELECT
    *,
    [rn] = ROW_NUMBER() 
           OVER ( 
             PARTITION BY 
               [ItemCode],
               [Leftover] / ABS( [Leftover] )
             ORDER BY
               [Leftover] DESC )
  FROM (
    SELECT
      a.[ItemCode],
      a.[Store],
      a.[Room],
      [Has] = a.[Quantity],
      [Need] = n.Quantity,
      [Leftover] = n.[Quantity] 
                 - SUM( a.[Quantity] ) 
                   OVER ( 
                     PARTITION BY 
                       a.[ItemCode]
                     ORDER BY 
                       a.[Store], 
                       a.[Room] 
                     ROWS BETWEEN 
                           UNBOUNDED PRECEDING 
                       AND CURRENT ROW )
    FROM
      @NeedStock n
      INNER JOIN @AvailableStock a ON ( 
            a.ItemCode = n.ItemCode )
    ) t
  ) t
WHERE
      [Leftover] > 0
  OR  [Leftover] < 0 AND [rn] = 1
ORDER BY
  1, 2, 3
1 дек 16, 12:48    [19954834]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Руслан Дамирович,

сложно читать такое форматирование :)
1 дек 16, 16:18    [19956221]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
o-o
Guest
моя первая версия была "фигуры из тетриса", в каком-то посте ну явно они просматривались.
теперь я склоняюсь к мысли, что у них там построчная оплата:
чем больше строк, тем больше платят.
развалить агрегат на 10 строк, это сильно!
SUM( a.[Quantity] ) 
                   OVER ( 
                     PARTITION BY 
                       a.[ItemCode]
                     ORDER BY 
                       a.[Store], 
                       a.[Room] 
                     ROWS BETWEEN 
                           UNBOUNDED PRECEDING 
                       AND CURRENT ROW )
1 дек 16, 17:23    [19956632]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Да что вы. Построчная оплата - это удел индусов.
У нас платят за все символы, включая пробелы. А за буквы в верхнем регистре - двойная оплата.
1 дек 16, 17:33    [19956698]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
o-o
Guest
Руслан Дамирович
платят за все символы, включая пробелы.

между прочим, у переводчиков именно так, правда, case insensitive.
но вообще правда нечитаемо,
хотя это конечно лучше, чем на каждой строке ровно 1 слово располагать
1 дек 16, 17:38    [19956721]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
o-o
но вообще правда не_читаемо

Я покорнейше прошу кусочек вашего божественно кода, дабы после причащения к оному я смог обратиться в вашу веру RPC (rightfully perfect code).
1 дек 16, 19:11    [19957038]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
o-o
но вообще правда нечитаемо,
А по моему, нормальный шаблон форматирования. Довольно типично для SQL, и для других ЯП.
Я вот примерно так и пишу, ну, плюс-минус, конечно.
1 дек 16, 21:50    [19957529]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
o-o
Guest
Руслан Дамирович,
Не выйдет, на работе стоит 2008 R2, а там нет агрегатных window functions, только ранжирующие.

Но если однажды over разопрет так, что перенос строки напросится, я не стану убиваться и в 1 стрoку впихивать.
А так да, у меня полная противоположность вашему, весь over на одной строке.
Вроде вы пересекались с моим кодом, там его еще aleks2 бредом обозвал
1 дек 16, 22:33    [19957692]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
alexeyvg
А по моему, нормальный шаблон форматирования.
Ага. Пока дочитал до конца предложения over, уже забыл чего суммируем
1 дек 16, 22:49    [19957740]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
invm
alexeyvg
А по моему, нормальный шаблон форматирования.
Ага. Пока дочитал до конца предложения over, уже забыл чего суммируем
Не, ну в OVER конечно перебор :-) Я его тоже в одну строку пишу, как и вы.
Это я в целом про форматирование говорю. С форматированием лучше перестараться, а не как тут иногда пишут, тупо наваливая кучу текста....
1 дек 16, 22:59    [19957769]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
o-o
Guest
invm
alexeyvg
А по моему, нормальный шаблон форматирования.
Ага. Пока дочитал до конца предложения over, уже забыл чего суммируем

"мы заботимся о развитии вашей памяти"

А давайте в связи с наступлением календарной зимы и приближением НГ откроем топик-конкурс на лучшую елку из кода!
Не чтобы результат выполнения был елкой, а само супер-форматирование напоминало бы.
Ну или чего там елки, давайте просто любые потрясшие вас фигуры выкладывать.
Только с условием неподдельности.
Ну т.е. реальные куски кода вывешивать, без приукрасов
1 дек 16, 23:01    [19957776]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Плохой шаблон, не видно целостности оператора.
В не не переносите слоги в словах.
ма
  ма
  мы
  ла
ра
  му
2 дек 16, 11:53    [19959041]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
т.е. атомарные операторы-выражения нельзя разбивать на строки, только в исключительных ситуациях.
2 дек 16, 11:55    [19959046]     Ответить | Цитировать Сообщить модератору
 Re: Построчный декремент на значение поля?  [new]
o-o
Guest
да все можно, вот например
Древние китайские, корейские и японские тексты записывались в вертикальные колонки, идущие справа налево
другое дело, что ключевые слова языка и всякие там идентификаторы попробуй запиши в колонку,
парсер закричит.
но не все потеряно, можно сделать 1 слово - 1 строка, что мы собственно и видим.
просто не всем дано понять красоту и удобство вертикального письма.
вот мне точно не дано
2 дек 16, 12:07    [19959097]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить