I need your help in writing a stored procedure to update the data given below into a table in SQL Server.
I have uploaded a picture to ensure format is set right.
The second column which identifies the part number, example: part 100 has 103 and 104 as its components in bill of materials. However only 1 (either 103 is allocated to id 1, part 100 or 104 is allocated to id 2, part 100).
For us to differentiate during the planning bill of materials for the top level assembly, we need to update assembly hashkey (string) with the last character of component used (ex: 103, use 3), 000111 becomes 300111. Doesn't matter which component gets assigned to the parent.
Can anybody please help me with this. thanks.
Data Structure Expected result
ID Part hashkey ID Part hashkey
----------------- --------------------
1 100 000111 1 100 300111
2 100 000111 2 100 400111
3 103 000111 3 103 000111
4 104 000111 4 104 000111
Data structure: expected_result
Ok, if I understand your requirement now, then this should do it:
WITH cteParent AS (
SELECT Id, hashkey,
ROW_NUMBER() OVER (Partition By hashkey, Order by ID) AS rn
FROM MyTable
WHERE Part=100
)
, cteComponent AS (
SELECT Id, hashkey,
ROW_NUMBER() OVER (Partition By hashkey, Order by ID) AS rn
FROM MyTable
WHERE Part<>100
)
SELECT t.Id, t.Part
, CASE
WHEN t.Part=100 THEN RIGHT(CAST(c.Part AS varchar(31)),1) + SUBSTRING(CAST(p.hashkey AS varchar(31)), 2, LEN(CAST(t.hashkey AS varchar(31)))-1)
ELSE CAST(t.hashkey AS varchar(31))
END AS hashkey
FROM MyTable t
LEFT OUTER JOIN cteParent p ON t.Id=p.Id
LEFT OUTER JOIN cteComponent c
ON p.hashkey=c.hashkey
AND p.rn=c.rn
ORDER BY ID asc