TSQL Comparing sets of similar and repeating items

Question

I have a table of orders waiting to be fulfilled and a table of returned orders. There is only one product but you can order in different quantity packs. My job is to pair these orders with returns but the return order must match exactly with the current order in terms of the number of packs ordered and quantity in each pack. So matching on the number of packs ordered is no issue but matching up the quantities is giving me a headache. My orders and returns are pipe delimited fields. An order/return of 3 packs of 30 each will look like "30|30|30". An order of 3 packs, 2 of 15 and 1 of 30 will look like "15|15|30", "15|30|15", or "30|15|15". An order of "15|15|30" can be paired up with a return of "15|30|15" as they are the same. I know I need to parse out the items in the fields into a table first. But how do I compare them?

I have gone through all the examples here: TSQL Comparing two Sets

• the intersect and cross join examples doesn't work when there are duplicates in the set - (a,a,b) and (b,a,a) do not match
• full join doesn't work 2 sets have different qtys of same elements - (a,a,c) (and a,c,c) is a match

So my thoughts at this point are to maybe parse into table, sort, reassemble back into ordered piped string and compare the 2 strings. That would work but is it the best way to do this?

Editing to add - I cannot change the data model. SQL Server 2017

Sample data (records 2 and 3 would be a match):

declare @comp table(
    OrderNo         int,
    OrderPackCount  int,
    OrderTtlPieces  int,
    OrderQtys       varchar (50),
    ReturnNo        int,
    RtnPackCount    int,
    RtnTtlPieces    int,
    RtnQtys         varchar(50))

insert into @comp values 
    (55500, 2, 100, '50|50|', 401, 2, 100, '75|25|'),
    (55501, 2, 60, '20|40|', 404, 2, 60, '40|20|'),
    (55504, 3, 75, '15|30|30|', 385, 7, 75, '30|15|30|'),
    (55508, 3, 90, '30|30|30|', 422, 7, 75, '50|30|10|')

Answer 1

Couple options to try.

Here's an example of splitting the values, reordering, putting them back to do the compare. Since you mention SQL2017 we can use STRING_SPLIT and then use STRING_AGG with WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) to reorder and concatenate the values.

SELECT      [comp].*
FROM        @comp [comp]
CROSS APPLY (
                SELECT   STRING_AGG([value], '|') WITHIN GROUP(ORDER BY [value]) AS [OrdStr]
                FROM     STRING_SPLIT([comp].[OrderQtys], '|')
                WHERE    [value] <> ''
            ) AS [ord]
CROSS APPLY (
                SELECT   STRING_AGG([value], '|') WITHIN GROUP(ORDER BY [value]) AS [RtnStr]
                FROM     STRING_SPLIT([comp].[RtnQtys], '|')
                WHERE    [value] <> ''
            ) AS [Rnt]
WHERE       [ord].[OrdStr] = [Rnt].[RtnStr];

Another option would be to identify those that do not match and then use EXCEPT. Split the values, aggregate and get a count by value, then outer apply where the values equal having the same count and then identify those that do not match. EXCEPT then returns values those that are not in that result.

SELECT *
FROM   @comp
EXCEPT
SELECT      [comp].*
FROM        @comp [comp]
OUTER APPLY (
                SELECT   [value] AS [OrdValue]
                       , COUNT(*) AS [RntCnt]
                FROM     STRING_SPLIT([comp].[OrderQtys], '|')
                WHERE    [value] <> ''
                GROUP BY [value]
            ) AS [ord]
OUTER APPLY (
                SELECT   [value] AS [RntValue]
                       , COUNT(*) AS [RntCnt]
                FROM     STRING_SPLIT([comp].[RtnQtys], '|')
                WHERE    [value] <> ''
                         AND [value] = [ord].[OrdValue]
                GROUP BY [value]
                HAVING   COUNT(*) = [ord].[RntCnt]
            ) AS [Rnt]
WHERE       [Rnt].[RntValue] IS NULL;