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

Откуда:
Сообщений: 141
Рискну задать чайниковый вопрос.

Есть 2 таблицы:

Таблица1:
OrdNoDatePartNameOrdQty
Заказ101/02/18Part15
Заказ201/01/18Part18
Заказ301/05/18Part13


Таблица2:
PartNameBalance
Part120
Part229


Надо выдать OrdNo, Date, PartName, OrdQty, Balance с распределением остатков по заказам с сортировкой по дате.

Требуемый результат выборки:
OrdNoDatePartNameOrdQtyBalance
Заказ201/01/18Part1820
Заказ101/02/18Part1512
Заказ301/05/18Part137


Как такое сделать без цикла и без курсора?
Заранее спасибо.
28 ноя 17, 18:42    [20989290]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
slovnet,

У вас баланс не сходится
28 ноя 17, 18:48    [20989309]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
slovnet
Member

Откуда:
Сообщений: 141
Почему?
После поставки заказов 2 и 1 остаётся 7.
28 ноя 17, 18:51    [20989316]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
slovnet

Надо выдать OrdNo, Date, PartName, OrdQty, Balance с распределением остатков по заказам с сортировкой по дате.

Переформулирую, коллега - "Надо выдать OrdNo, Date, PartName, OrdQty, Balance с распределением остатков на начало исполнения заказа по заказам с сортировкой по дате. Цель - проверить, не было ли кривых документов отгрузки по заказу уже схлопнутого в ноль остатка".
По факту - реализуется оконной функцией T-SQL. Думаю, коллеги сейчас ссылок накидают.
28 ноя 17, 19:00    [20989335]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
slovnet
Member

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

Ну, конкретная бизнес-задача несколько иная.
Это для отдела планирования. Им надо глянуть есть ли достаточно готовой продукции на складе, сколько пустить в производство.

Но мне бы синтаксис. T-SQL не моя область.
28 ноя 17, 19:11    [20989356]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Нарастающий итог.
Чистая теория. Можно рукалицеить.

2012+:
[qty] = SUM( ... ) OVER ( PARTITION BY [PartName] ROWS BETWEEN UNBOUNDED PRECEDING AND [CURRENT ROW|1 PRECEDING ROW] )


2008-:
WITH 
t AS (
  SELECT
     *,
     [rn] = ROW_NUMBER() OVER ( PARTITION BY [PartName] ORDER BY [date|ord_no] DESC )
)
SELECT
  *
FROM
  t t1
  OUTER APPLY (
     SELECT
       [qty] = SUM( ... )
     FROM
        t t2
     WHERE
       t2.[PartName] = t1.[PartName]
       AND t2.[rn] <= t1.[rn]
  ) ss
28 ноя 17, 19:19    [20989369]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
slovnet
Member

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

Спасибо! будем играться.
28 ноя 17, 19:24    [20989371]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
В последнем варианте накосячил - или ORDER BY .... (без DESC) или t2.[rn] > t1.[rn]
28 ноя 17, 19:53    [20989417]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
slovnet
Member

Откуда:
Сообщений: 141
Спасибо
28 ноя 17, 20:04    [20989433]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
with
  [Таблица1] as(
    select OrdNo,cast([Date] as date) [Date],PartName,OrdQty
    from(
      values(N'Заказ1','2018-02-01','Part1',5),
            (N'Заказ2','2018-01-01','Part1',8),
            (N'Заказ3','2018-03-01','Part1',3)
    )t(OrdNo,[Date],PartName,OrdQty)
  ),
  [Таблица2] as(
    select *
    from(
      values('Part1',20),('Part2',29)
    )t(PartName,Balance)
  )
select t1.OrdNo,t1.[Date],t1.PartName,t1.OrdQty,
  t2.Balance+t1.OrdQty-
  sum(t1.OrdQty)
    over(partition by t1.PartName
         order by t1.[Date]
         rows unbounded preceding) Balance
from [Таблица1] t1 join [Таблица2 ]t2
  on t1.PartName=t2.PartName
28 ноя 17, 21:50    [20989638]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
slovnet
Member

Откуда:
Сообщений: 141
andrey odegov,

Спасибо
28 ноя 17, 22:37    [20989804]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
slovnet
Member

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

Разобрался. Попутно много понял.
Огромное спасибо.
30 ноя 17, 19:14    [20996413]     Ответить | Цитировать Сообщить модератору
 Re: Аллокация без цикла и без курсора  [new]
Goga-Gola
Guest
slovnet,

    DECLARE @Orders TABLE (OrdNo VARCHAR(20), [Date] Date, PartName VARCHAR(20), OrdQty INT);
    DECLARE @Balance TABLE (PartName VARCHAR(20), Balance INT);

    INSERT @Orders VALUES
        ('Заказ1', '1.2.18', 'Part1', 5),
        ('Заказ2', '1.1.18', 'Part1', 8),
        ('Заказ3', '1.5.18', 'Part1', 3);

    INSERT @Balance VALUES
        ('Part1', 20),
        ('Part2', 29);

    SELECT OrdNo, 
           [Date],
           Orders.PartName,
           OrdQty,
           Balance - IsNull(SUM(OrdQty) OVER (PARTITION BY Orders.PartName  ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS Total31
      FROM @Orders AS Orders
INNER JOIN @Balance as Balance ON Orders.PartName = Balance.PartName
4 дек 17, 16:18    [21004883]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить