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 28-Mar-24 8:06am

0x01AA

Updated 28-Mar-24 23:44pm

v6

Add a Solution

2 solutions

Solution 2

Below my solution.
I don't like it, because it is a way too clumsy.
I would never use that in production code. But for an analysis of an existing database it is just about acceptable.

Many thanks to @AndreOosthuizen[^]

WITH Boms AS (
SELECT  DISTINCT
	BomId
  FROM BomPositions
),
BomSetId AS (
   SELECT
      Boms.BomId,
	  (SELECT STRING_AGG(BomPos.MaterialId + '_' + FORMAT(BomPos.MaterialQuantity, 'N2'), ',') WITHIN GROUP (ORDER BY BomPos.MaterialId, BomPos.MaterialQuantity)
      FROM BomPositions BomPos 
	  WHERE  (BomPos.BomId = Boms.BomId)
	  )  AS SetId
   FROM Boms 
),
BomSets AS (
SELECT 
	SetId, 
	MIN(BomId) MinBomId,
	COUNT(*) BomCount
FROM BomSetId
GROUP BY SetId
)

SELECT DISTINCT * 
FROM BomSets
LEFT JOIN BomPositions ON BomPositions.BomId = MinBomId
ORDER BY SetId,MaterialId

[Edit]
ChatGPT documented our collaborative solution (90%ChatGPT, 10% me) as follows, not bad from my point of view:

Explanation:
- **Boms CTE:** Selects distinct BomIds from the BomPositions table.
- **BomSetId CTE:** For each BomId, creates a SetId by concatenating MaterialId and MaterialQuantity with an underscore `_`.
- **BomSets CTE:** Groups the BomSetId by SetId, selects the minimum BomId (as a representative Bom), and counts the number of BOMs with the same SetId.
- **Main Query:** Joins the BomSets with BomPositions to retrieve the unique BOMs and their details.

Posted 31-Mar-24 1:34am

0x01AA

Updated 31-Mar-24 5:38am

v3

Comments

0x01AA 31-Mar-24 11:28am

And no, it was not me who accpeted that as a solution .... ?
See also: Bugs and Suggestions[^]

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 · Accepted Answer · 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]