Блог

DDL и DML:

 CREATE TABLE Product 
(
    id int not null,
    code varchar(20),
    description varchar(40),
    searchref1 varchar(20),
    weightKG decimal(10, 2)
);

INSERT INTO Product (id, code, description, searchref1, weightKG)
VALUES (1, '0001101', 'ProductOne', '1kg', 1),
       (2, '0001050', 'ProductOne', '500g', 0.5),
       (3, '0001400', 'ProductOne', '10 x 1kg', 10),
       (4, '0080101', 'ProductTwo', '1kg', 1),
       (5, '0080050', 'ProductTwo', '500g', 0.5),
       (6, '0001WIP', 'ProductOne Work in progress', '1kg', 1),
       (7, '0080WIP', 'ProductTwo Work in progress', '1kg', 1);
 
CREATE TABLE SalesOrderLine 
(
    id int not null,
    productID int,
    requiredQty int
);

INSERT INTO SalesOrderLine (id, productID, requiredQty)
VALUES (1, 1, 21), (2, 2, 5), (3, 3, 8), (4, 5, 1);

CREATE TABLE Stock 
(
    id int not null,
    productID int,
    inStock int
);

INSERT INTO Stock (id, productID, inStock)
VALUES (1, 6, 10), (2, 7, 2);
 

Ссылка на скрипку SQL: http://sqlfiddle.com /#!18/a68d49/28

У меня есть SalesOrderLine таблица, подобная:

 SELECT
    code, searchref1, requiredQty, weightKG
FROM 
    SalesOrderLine sol
JOIN 
    Product P ON p.id = sol.productid;

code       searchref1     requiredQty   weightKG
------------------------------------------------
0001101    1kg            21            1
0001050    500g           5             0.5
0001400    10 x 1kg       8             10
0080050    500g           1             0.5
 

Теперь я могу сгруппировать коды продуктов по первым 4 кодам. Пока я это делаю, я могу получить общую сумму weightKG * requiredQty .

Запрос

 SELECT
    LEFT(code, 4) AS code, SUM(weightKG * requiredQty) AS TotalKg
FROM  
    SalesOrderLine sol
JOIN 
    Product P ON p.id = sol.productid
GROUP BY 
    LEFT(code, 4)
 

Результат:

 code    TotalKg
---------------
0001    103.5
0080    0.5
 

Пока я знаю, сколько kg всего требуется для конкретного продукта.

Проблема: теперь я застрял с этим..

У меня есть другая таблица, которая называется Stock :

Запрос

 SELECT 
    LEFT(p.code, 4) AS code, p.searchref1, inStock  
FROM 
    stock s 
JOIN 
    product p ON s.productID = p.id;

code    searchref1  inStock
---------------------------
0001    1kg         10
0080    1kg         2
 

Теперь мне нужно получить этот вывод таблицы:

 code    TotalKg   inStock1KgOnly  remaining
-------------------------------------------
0001    103.5     10              93.5          
0080    0.5       0               0.5
 

Причина 0080 в том, что отображается как 0 , потому что в строке SalesOrderLine нет 1kg заказов и не имеет отношения к запасу. Оставшееся показывает, сколько запасов необходимо создать для конкретного 1kg создания .

remaining Столбец — это просто простое вычисление TotalKg - inStock1KgOnly .

Моя попытка запроса:

 SELECT 
    t1.code, t1.totalKg, 
    t2.inStock AS inStock1KgOnly, 
    (t1.TotalKg - t2.inStock) AS Remaining 
FROM
    (SELECT
         LEFT(code, 4) AS code, SUM(weightKG * requiredQty) AS TotalKg
     FROM 
         SalesOrderLine sol
     JOIN 
         Product P ON p.id = sol.productid
     GROUP BY 
         LEFT(code, 4)) t1 
JOIN 
    (SELECT LEFT(p.code, 4) as Code, inStock
     FROM product p
     JOIN stock s ON s.productID = p.id) t2 ON (t1.code = t2.code)
 

Вывод

 code    totalKg inStock1KgOnly  Remaining
------------------------------------------
0001    103.5   10              93.5
0080    0.5     2               -1.5
 

Отредактировано, чтобы вывести требования, не объясненные явно.

Это должно охватить вас…

 WITH TEMP_Product AS
(
SELECT
  p.Id AS ProductId
  ,LEFT(p.code, 4) AS Code
  ,p.weightKg AS WeightKg
FROM Product p
)
,TEMP_SalesOrderLine AS
(
SELECT 
  p.Code
  ,SUM(l.RequiredQty * p.WeightKg) AS TotalKg
FROM SalesOrderLine l
INNER JOIN TEMP_Product p ON l.ProductId = p.ProductId
GROUP BY p.Code
)
,TEMP_Stock AS
(
SELECT DISTINCT
  p.Code
  ,s.InStock
  ,SUM(s.InStock * p.WeightKg) OVER (PARTITION BY p.Code) AS InStockKg
FROM Stock s
INNER JOIN TEMP_Product p ON p.ProductId = s.ProductId
)
,TEMP_Summary AS
(
SELECT
  l.Code
  ,l.TotalKg
  ,FLOOR(l.TotalKg) AS FloorKg
  ,s.InStockKg
  ,(l.TotalKg - s.InStockKg) AS Delta
FROM TEMP_SalesOrderLine l
INNER JOIN TEMP_Stock s ON l.Code = s.Code
)
SELECT
  s.Code
  ,s.TotalKg
  ,CASE WHEN (s.FloorKg < 1) THEN 0 
        ELSE CASE WHEN (s.Delta >= 0) THEN s.InStockKg
                  ELSE s.TotalKg END 
   END AS InStockKg
  ,CASE WHEN (s.FloorKg < 1) THEN s.TotalKg
        ELSE CASE WHEN (s.Delta < 0) THEN 0 
                  ELSE s.Delta END
   END AS Remaining
FROM TEMP_Summary s;
 

Результаты:

0.5

 INSERT INTO SalesOrderLine (id, productID, requiredQty)
VALUES (1, 1, 21), (2, 2, 5), (3, 3, 8), (4, 5, 1);
 

1.0

 INSERT INTO SalesOrderLine (id, productID, requiredQty)
VALUES (1, 1, 21), (2, 2, 5), (3, 3, 8), (4, 5, 2);
 

2.5

 INSERT INTO SalesOrderLine (id, productID, requiredQty)
VALUES (1, 1, 21), (2, 2, 5), (3, 3, 8), (4, 5, 5);