Systems Engineering and RDBMS

Grouping same sets together

Posted by decipherinfosys on February 23, 2008

Last night, at one of the client sites, a quick piece of code needed to be written which would take care of grouping the same cases (physical boxes) with the exact same set of SKUs together. It was a simple bit of SQL in the overall complex algorithm which decides how the cases are packed and shipped. Below is a small explanation of the issue and a short snippet of code to resolve it.

Let’s assume that there is a table called TEMP_CASE which has two columns: CASE_NBR and SKU_ID. And the data distribution is as shown below:

SET NOCOUNT ON
GO

CREATE TABLE TEMP_CASE (CASE_NBR VARCHAR(10), SKU_ID VARCHAR(10));
INSERT INTO TEMP_CASE VALUES (‘C1’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C1’, ‘S2’);
INSERT INTO TEMP_CASE VALUES (‘C2’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C2’, ‘S2’);
INSERT INTO TEMP_CASE VALUES (‘C3’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C3’, ‘S4’);
INSERT INTO TEMP_CASE VALUES (‘C4’, ‘S2’);
INSERT INTO TEMP_CASE VALUES (‘C4’, ‘S3’);
INSERT INTO TEMP_CASE VALUES (‘C4’, ‘S4’);
INSERT INTO TEMP_CASE VALUES (‘C4’, ‘S5’);
INSERT INTO TEMP_CASE VALUES (‘C5’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C5’, ‘S4’);
INSERT INTO TEMP_CASE VALUES (‘C6’, ‘S1’);
INSERT INTO TEMP_CASE VALUES (‘C6’, ‘S4’);
INSERT INTO TEMP_CASE VALUES (‘C7’, ‘S7’);
INSERT INTO TEMP_CASE VALUES (‘C7’, ‘S8’);

The intent of the code was to group C1 and C2 together since both of them had S1 and S2 as the SKUs in them. Likewise, C3, C5 and C6 would be grouped together since they have S1 and S4 as the SKUs. While C4 and C7 will be totally separate since the SKUs that the “EXACT” combination of SKUs that exist in them do not exist in any other case. There are many ways of doing that – key thing to keep in mind is that not only the number of SKUs need to be the same, the SKUs should match up exactly.

SELECT DISTINCT
DENSE_RANK() OVER (ORDER BY IV1.COMP_SKU) AS GRP_NBR,
IV1.CASE_NBR,
IV1.COMP_SKU
FROM
(SELECT
A.CASE_NBR, STUFF((SELECT ‘,’ + SKU_ID FROM TEMP_CASE WHERE CASE_NBR = A.CASE_NBR ORDER BY SKU_ID FOR XML PATH(”)), 1, 1, ”) AS COMP_SKU
FROM TEMP_CASE AS A) AS IV1
LEFT OUTER JOIN
(SELECT
A.CASE_NBR, STUFF((SELECT ‘,’ + SKU_ID FROM TEMP_CASE WHERE CASE_NBR = A.CASE_NBR ORDER BY SKU_ID FOR XML PATH(”)), 1, 1, ”) AS COMP_SKU
FROM TEMP_CASE AS A) AS IV2
ON IV1.COMP_SKU = IV2.COMP_SKU
ORDER BY GRP_NBR
GO
GRP_NBR CASE_NBR COMP_SKU
——————– ———- ————
1 C1 S1,S2
1 C2 S1,S2
2 C3 S1,S4
2 C5 S1,S4
2 C6 S1,S4
3 C4 S2,S3,S4,S5
4 C7 S7,S8

There are other ways to accomplish this as well but the above was performant enough for the requirements. Needless to state, there was additional filter criteria and conditions in the SQL that was used.


Sorry, the comment form is closed at this time.

 
%d bloggers like this: