How to find equal 'BOMS' from a bom- positions table

Question

0.00/5 (No votes)

See more:

Dear experts, I'm lost, please help me.

I have a table with BOM Positions:

BomId   MaterialId   MaterialQuantity   Note
-----   ----------   ----------------   -----
Bom_A   Mat_A        20                 BOM1
Bom_A   Mat_B        30                 BOM1
Bom_B   Mat_A        20                 BOM2
Bom_B   Mat_B        35                 BOM2
Bom_C   Mat_A        20                 BOM1
Bom_C   Mat_B        30                 BOM1
Bom_D   Mat_A        10                 BOM3
Bom_D   Mat_B        20                 BOM3
Bom_D   Mat_C        30                 BOM3
Bom_E   Mat_A        10                 BOM3
Bom_E   Mat_B        20                 BOM3
Bom_E   Mat_C        30                 BOM3

Based on that table I like to select distinct the equal BOMs.
Equal here means the BomDetails have same material in same quantities.

Note: The column 'Note' is only for the explanation here and does not exists in my db.

Therefore the result I'm looking for is something like this:

MaterialId	 MaterialQuantity   BOMGRP   BOMCOUNT
----------   ----------------   ------   ---------
Mat_A        20                 1        2
Mat_B        30                 1        2
Mat_A        20                 2        1
Mat_B        35                 2        1
Mat_A        10                 3        2
Mat_B        20                 3        2
Mat_C        30                 3        2

'BOMGRP' should be a dynamically created Id.
'BOMCOUNT' would be nice, but I can live without it

I hope my question makes sense.
Thank you very much in advance.

What I have tried:

I tried to solve it with the help of ChatGPT, but without success.
~~Btw. I'm on MS SQL Server 2014 :(~~
[Edit]: Upgraded to MS SQL Server 2019 :)

DROP TABLE BomPositions;
CREATE TABLE BomPositions
(
   BomId             VARCHAR(30),
   MaterialId        VARCHAR(30),
   MaterialQuantity  DOUBLE PRECISION,
   Note				 VARCHAR(30)
);


INSERT INTO BomPositions(BomId, MaterialId, MaterialQuantity, Note) VALUES
   ('Bom_A', 'Mat_A', 20, 'BOM1'),
   ('Bom_A', 'Mat_B', 30, 'BOM1'),
   ('Bom_B', 'Mat_A', 20, 'BOM2'),
   ('Bom_B', 'Mat_B', 35, 'BOM2'),
   ('Bom_C', 'Mat_A', 20, 'BOM1'),
   ('Bom_C', 'Mat_B', 30, 'BOM1'),
   ('Bom_D', 'Mat_A', 10, 'BOM3'),
   ('Bom_D', 'Mat_B', 20, 'BOM3'),
   ('Bom_D', 'Mat_C', 30, 'BOM3'),
   ('Bom_E', 'Mat_A', 10, 'BOM3'),
   ('Bom_E', 'Mat_B', 20, 'BOM3'),
   ('Bom_E', 'Mat_C', 30, 'BOM3');

SELECT * FROM BomPositions ORDER BY BomId, MaterialId;

Posted yesterday

0x01AA

Updated 19hrs ago

v6

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andre Oosthuizen · Answer 1 · 2024-03-29T01:10:00

The query should look like -

SQL

WITH BOMGroups AS (
    SELECT DISTINCT BomId, ROW_NUMBER() OVER (ORDER BY BomId) AS BOMGRP
    FROM BomPositions
),
BOMCounts AS (
    SELECT BomId, COUNT(DISTINCT MaterialId) AS BOMCOUNT
    FROM BomPositions
    GROUP BY BomId
)
SELECT MaterialId,
       MaterialQuantity,
       BOMGRP,
       BOMCOUNT
FROM (
    SELECT MaterialId,
           MAX(MaterialQuantity) AS MaterialQuantity,
           BomId,
           ROW_NUMBER() OVER (PARTITION BY BomId ORDER BY MaterialId) AS RowNum
    FROM BomPositions
    GROUP BY MaterialId, BomId
) AS SubQuery
JOIN BOMGroups ON SubQuery.BomId = BOMGroups.BomId
JOIN BOMCounts ON SubQuery.BomId = BOMCounts.BomId
ORDER BY BOMGRP, MaterialId;

I have not tested though.

[EDIT]

SQL

WITH BOMGroups AS (
    SELECT
        BomId,
        ROW_NUMBER() OVER (ORDER BY BomId) AS BOMGRP
    FROM
        (SELECT DISTINCT BomId FROM BomPositions) AS DistinctBomIds
),
BOMCounts AS (
    SELECT
        BomId,
        COUNT(DISTINCT MaterialId) AS BOMCOUNT
    FROM
        BomPositions
    GROUP BY
        BomId
),
MaterialRanks AS (
    SELECT
        BomId,
        MaterialId,
        MAX(MaterialQuantity) AS MaterialQuantity,
        DENSE_RANK() OVER (PARTITION BY BomId ORDER BY MaterialId) AS MaterialRank
    FROM
        BomPositions
    GROUP BY
        BomId,
        MaterialId
)
SELECT
    MaterialId,
    MaterialQuantity,
    BOMGRP,
    BOMCOUNT
FROM
    MaterialRanks
    JOIN BOMGroups ON MaterialRanks.BomId = BOMGroups.BomId
    JOIN BOMCounts ON MaterialRanks.BomId = BOMCounts.BomId
ORDER BY
    BOMGRP,
    MaterialRank;

[END EDIT]