Complex SQL query for inventory app

Question

Given the following 2 tables, I need to find the warehouses that have all the parts in the right quantity to build a particular kit, or more appropriately, how many kits each can warehouse can build.

Inventory table: Warehouse, Part, and QuantityOnHand
Kit table: Kit, Part, QuantityForKit

For example: Kit1 requires 1 of Part1, 2 of Part2, and 1 of Part3. Warehouse A has 20 Part1, 5 Part2 and 3 Part3. Warehouse B has 5 Part1, 10 Part2, and no Part3.

Warehouse A can only build 2 of Kit1 because it doesn't have enough Part2 to make more than 2 kits. Warehouse B can't build any Kit1 because it doesn't have all the necessary parts.

I've got the following demo that works, but it seems really cumbersome and uses mostly table/index scans. Our inventory table is large and this just runs too slow. I'm looking for a better way to accomplish the same thing. In the demo there's an unbounded cross join, but in the actual app, it's limited to a single kit.

CREATE TABLE #warehouse
(
    Warehouse CHAR(1) NOT NULL PRIMARY KEY
)
INSERT INTO #warehouse VALUES ('A')
INSERT INTO #warehouse VALUES ('B')
INSERT INTO #warehouse VALUES ('C')
INSERT INTO #warehouse VALUES ('D')

CREATE TABLE #inventory
(
    Warehouse CHAR(1) NOT NULL ,
    Part INT NOT NULL ,
    OnHand INT NOT NULL ,
    CONSTRAINT pk_inventory PRIMARY KEY CLUSTERED (Part, Warehouse)
)
INSERT INTO #inventory VALUES ('A', 1, 20)
INSERT INTO #inventory VALUES ('A', 2, 5)
INSERT INTO #inventory VALUES ('A', 3, 3)
INSERT INTO #inventory VALUES ('B', 1, 5)
INSERT INTO #inventory VALUES ('B', 2, 10)
INSERT INTO #inventory VALUES ('C', 1, 1)
INSERT INTO #inventory VALUES ('C', 3, 1)
INSERT INTO #inventory VALUES ('D', 1, 1)
INSERT INTO #inventory VALUES ('D', 2, 2)
INSERT INTO #inventory VALUES ('D', 3, 1)

CREATE TABLE #kit
(
    Kit INT NOT NULL ,
    Part INT NOT NULL ,
    Quantity INT NOT NULL ,
    CONSTRAINT pk_kit PRIMARY KEY CLUSTERED (Kit, Part)
)
INSERT INTO #kit VALUES (1, 1, 1)
INSERT INTO #kit VALUES (1, 2, 2)
INSERT INTO #kit VALUES (1, 3, 1)
INSERT INTO #kit VALUES (2, 1, 1)
INSERT INTO #kit VALUES (2, 2, 1)


    -- Here's the statement I need to optimize
SELECT 
    Warehouse, 
    Kit, 
    MIN(Capacity) AS [Capacity]
FROM
    (
    SELECT 
        A.Warehouse, 
        A.Kit, 
        A.Part, 
        ISNULL(B.OnHand, 0) AS [Quantity], 
        ISNULL(B.OnHand, 0) / A.Quantity AS Capacity 
    FROM 
        (
        SELECT * 
        FROM 
            #warehouse
            CROSS JOIN
                            -- (SELECT * FROM
            #kit
                            -- WHERE #kit.Kit = @Kit) K
        ) A
        LEFT OUTER JOIN
        #inventory B
            ON A.Warehouse = B.Warehouse
            AND A.Part = B.Part
    ) C
GROUP BY
    Warehouse, 
    Kit
;

Suggestions appreciated.

This sounds more like an optimization problem. You haven't specified which Kits are more valuable. For example: imagine Warehouse 1 has tons of Part 2 and Part 3 but is down to its last Part 1. Well, Kit 1 and Kit 2 both need exactly one Part 1. So which Kit to build? — ExactaBox, Dec 22 '12 at 04:03
This is for SQL Server. Value of kits is irrelevant as it's really a query to see which warehouse to send the order, not to create kits for stock. — Lee Greco, Dec 22 '12 at 21:48

score 1 · Answer 1 · answered Dec 24 '12 at 19:22

1

Try this:

SELECT warehouse, MIN(capacity) FROM (
  SELECT i.warehouse, i.onhand / k.quantity as capacity
  FROM #kit k
  JOIN #inventory i
    ON k.part = i.part AND k.quantity <= i.onhand
  WHERE k.kit = @kit) c
GROUP BY warehouse
HAVING COUNT(*) = (SELECT COUNT(*) FROM #kit WHERE kit = @kit)

sqlfiddle here

answered Dec 24 '12 at 19:22

PinnyM

35,165
3
73
81

This does work nicely for the example data as there is only a single entry per warehouse for a part in the inventory table. Unfortunately for me it is an over simplification as I have to deal with many records for an item in a warehouse - think: location, serial number, although I can summarize part per warehouse and use that instead of the inventory table directly. Thanks for the alternative approach. If it performs better than what I have I'll mark it as the answer. – Lee Greco Dec 28 '12 at 01:57
@LeeGreco: If there are more variables involved, please show them in the schema so I can revise my answer. – PinnyM Dec 28 '12 at 14:23
@PinnyM This is very similar to what I'm trying to figure out. I have everything posted on Stack. Would you be able to take a look and offer any expertise? https://stackoverflow.com/q/51083593/9926399 – SMHorus Jun 29 '18 at 13:59

Complex SQL query for inventory app

1 Answers1