Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
Заявки-приходы
Guest
Дано: таблица заявок:
create table dbo.Claims (
  id int not null identity primary key,
  сreated datetime not null,
  item varchar(50) not null,
  quantity float
)

и таблица приходов по этим заявкам:
create table dbo.Incomings (
  id int not null identity primary key,
  item varchar(50) not null,
  quantity float
)

Необходимо заполнить таблицу соответствия приходов заявкам:
create table zcx.IncomingLinks (
  id int identity not null primary key,
  claim_id int not null,
  incoming_id int not null,
  quantity float not null,
  constraint fk_claim_id foreign key (claim_id) references dbo.Claims (id),
  constraint fk_incoming_id foreign key (incoming_id) references dbo.Incomings (id)
)

Связь прихода и заявки - по полю item
Нюансы:
1. В позиции прихода может быть меньшее количество, чем в заявке - тогда вставляем именно то количество, которое в приходе; при появлении следующего прихода на тот же item добавляем новую запись - и так до тех пор, пока количество, указанное в заявке, не будет меньшим или равным суммарному количеству по приходам.
2. В приходах на отдельно взятый item количество записей может быть меньше, чем заявок на этот же item - в это случае заявки привязываются по возрастанию срока создания. Заявки, на которые не хватило прихода, оставляем в ожидании следующих приходов.
3. Если в приходе количество больше, чем в отдельно взятой заявке, то на заявку распределяем столько, сколько указано в заявке, остаток распределяем на следующую заявку в соответствии с приоритетом (сроком) заявки, остаток, ели он еще есть - на следующую, итд, пока не выберем всё количество по приходу.
4. Если по приходу всё имеющееся количество распределено по заявкам, то такие приходы для связывания больше не рассматриваются.
Пример:
Заявки:
idcreateditemquantity
101.01.2015Петрушка1
202.01.2015Укроп2
303.01.2015Петрушка50
404.01.2015Укроп50
505.01.2015Килька50

Приходы:
iditemquantity
1Петрушка40
2Укроп40
3Килька40

Распределение:
idclaim_idincoming_idquantity
1111
23139
3222
44238
55340

(и при наличии следующих приходов на заявку 3 надо будет дораспределить 11б на заявку 4 - 12, на заявку 5 - 10).
Пока удалось родить только простыню мрачных процедурных конструкций с курсорами и if'ами. Вопрос: можно ли вышеописанное сделать запросом?
Делается для 2005-го сервера.
30 июн 15, 09:14    [17831066]     Ответить | Цитировать Сообщить модератору
 Re: Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Заявки-приходы
Вопрос: можно ли вышеописанное сделать запросом?


а кто вызывать запрос то будет ?
30 июн 15, 09:36    [17831126]     Ответить | Цитировать Сообщить модератору
 Re: Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
Заявки-приходы
Guest
Maxx
а кто вызывать запрос то будет ?

Клиентское приложение (задание по расписанию в windows scheduler'е).
30 июн 15, 09:40    [17831139]     Ответить | Цитировать Сообщить модератору
 Re: Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Заявки-приходы
Maxx
а кто вызывать запрос то будет ?

Клиентское приложение (задание по расписанию в windows scheduler'е).

и вы каждый раз будете ве пересчитывать ?
Просто любопытно ..а так ,то путь решения есть в ФАКЕ по форуму.
30 июн 15, 09:42    [17831145]     Ответить | Цитировать Сообщить модератору
 Re: Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
Заявки-приходы
Guest
Maxx
и вы каждый раз будете ве пересчитывать ?

Нет, не всё - только по приходам и заявкам, не поставленным в соответствие, и по которым не покрыто всё количество. От периодичности пересчета тут в любом случае не уйти, т.к. будут появляться новые заявки/приходы, которые нужно связывать. При определенном периоде пересчета (думаю, 1 раз в час) объем пересчитываемых данных будет не такой уж и большой.
Maxx
Просто любопытно ..а так ,то путь решения есть в ФАКЕ по форуму.

подразумевается статья про нарастающий итог?
30 июн 15, 09:59    [17831210]     Ответить | Цитировать Сообщить модератору
 Re: Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Заявки-приходы
При определенном периоде пересчета (думаю, 1 раз в час) объем пересчитываемых данных будет не такой уж и большой.


я , конечно,не на что не намекаю...но у вас нет не одного поля в таблицах которое рассказало что считать не надо

Заявки-приходы
подразумевается статья про нарастающий итог?

И она тоже,а так списание методом FIFO
30 июн 15, 10:22    [17831307]     Ответить | Цитировать Сообщить модератору
 Re: Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
Заявки-приходы
Guest
В приниципе, все вполне работает вот так:
declare
  cr cursor local for
    with cte as (
      select
        l.id, l.item, l.created, l.quantity,
        l.quantity-isnull(f.purchased, 0) rest,
        row_number() over(partition by l.item order by l.created) priority
      from dbo.claims l
      outer apply (
        select sum(i.quantity)
        from dbo.IncomingLinks i
        where i.claim_id=l.id
      )f(purchased)
    )
    select id, item, rest from cte
    where rest>0
    order by item desc, priority
declare
  @id int,
  @incoming_id int,
  @item varchar(50),
  @rest float,
  @purchased float;
open cr;
while 1=1
begin
  fetch next from cr into @id, @item, @rest;
  if @@fetch_status<>0 break;
  while 1=1
  begin
    set @purchased=null;
    select top 1
      @incoming_id=id,
      @purchased=case when i.quantity - isnull(f.purchased, 0)>@rest then @rest else i.quantity - isnull(f.purchased, 0) end
    from dbo.Incomings i
    outer apply (
      select sum(l.quantity)
      from dbo.IncomingLinks l
      where l.incoming_id=i.id
    )f(purchased)
    where i.item=@item and i.quantity>isnull(f.purchased, 0)
    order by i.id
    if @purchased is null break;
    insert into dbo.IncomingLinks(claim_id, incoming_id, quantity)
      values(@id, @incoming_id, @purchased);
    if @purchased=@rest break;
    set @rest=@rest-@purchased;
  end;
end;
close cr;
deallocate cr;

- но вот эта процедурная простыня мне не нравится. Попробовал запрос внутри курсорного цикла переделать в рекурсивное cte - не выходит, в них нельзя использовать top и агрегаты. А что еще тут можно сделать, как-то не соображу.
30 июн 15, 12:13    [17832121]     Ответить | Цитировать Сообщить модератору
 Re: Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8839
Заявки-приходы, Вам шашечки или ехать?
30 июн 15, 12:23    [17832172]     Ответить | Цитировать Сообщить модератору
 Re: Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
https://www.sql.ru/forum/1018035/zadachka-pro-yabloki-ili-sliyanie-2h-tablic
30 июн 15, 12:31    [17832216]     Ответить | Цитировать Сообщить модератору
 Re: Покрытие заявок приходами по приоритетам - как обойтись без процедурной логики?  [new]
Заявки-приходы
Guest
Владислав Колосов
Заявки-приходы, Вам шашечки или ехать?

Мне ехать, но побыстрее - не раз сталкивался с ситуацией, когда в MSSQL процедурная логика ощутимо медленне запросной.
invm
https://www.sql.ru/forum/1018035/zadachka-pro-yabloki-ili-sliyanie-2h-tablic

Ага, спасибо, именно то, что нужно. Получилось так:
;with cte as (
  select
    l.id, l.item, l.created, l.quantity,
    l.quantity-isnull(f.purchased, 0) rest,
    row_number() over(partition by l.item order by l.created) priority
  from dbo.claims l
  outer apply (
    select sum(i.quantity)
    from dbo.IncomingLinks i
    where i.claim_id=l.id
  )f(purchased)
  where l.quantity-isnull(f.purchased, 0)>0
), Claims as (
  select
    t1.id, t1.item, t1.rest,
    isnull(sum(t2.rest), 0) + 1 as p_QuantityStart, isnull(sum(t2.rest), 0) + t1.rest as p_QuantityEnd
  from cte t1
  left join cte t2 on t2.item=t1.item and t2.priority<t1.priority
  group by t1.id, t1.item, t1.rest
), cte2 as (
  select
    i.id, i.item, i.quantity-isnull(f.[distributed], 0) rest,
    row_number() over(partition by i.item order by i.id) priority
  from dbo.Incomings i
  outer apply (
    select
      sum(l.quantity)
    from dbo.IncomingLinks l
    where l.incoming_id=i.id
  )f([distributed])
  where i.quantity-isnull(f.[distributed], 0)>0
), Incomings as (
  select
    t1.id, t1.item, t1.rest,
    isnull(sum(t2.rest), 0) + 1 as g_QuantityStart, isnull(sum(t2.rest), 0) + t1.rest as g_QuantityEnd
  from cte2 t1
  left join cte2 t2 on t2.item=t1.item and t2.priority<t1.priority
  group by t1.id, t1.item, t1.rest
)
insert into dbo.IncomingLinks(claim_id, incoming_id, quantity)
select
  p.id, g.id,
 case
  when p.p_QuantityStart >= g.g_QuantityStart and p.p_QuantityEnd <= g.g_QuantityEnd then p.rest
  when g.g_QuantityStart >= p.p_QuantityStart and g.g_QuantityEnd <= p.p_QuantityEnd then g.rest
  when g.g_QuantityStart between p.p_QuantityStart and p.p_QuantityEnd then p.p_QuantityEnd - g.g_QuantityStart + 1
  when g.g_QuantityEnd between p.p_QuantityStart and p.p_QuantityEnd then g.g_QuantityEnd - p.p_QuantityStart + 1
 end
from Claims p
join Incomings g on
  g.item=p.item and
  p.p_QuantityStart <= g.g_QuantityEnd and
  p.p_QuantityEnd >= g.g_QuantityStart
order by 1,2
30 июн 15, 15:27    [17833323]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить