Solution to the Matching sets problem

This is in response to my earlier matching sets post.

I actually figured this out not too long after the original post, but then life happened (and the project that needed this was fundamentally re-defined, making the question obsolete).

So today I was doing some clean-up work and ran into to the code that I was able to use to solve the problem and realized that I never posted it.

The only real solution I came up with is a fairly brute force solution: Take all of my groups and render them as ordered lists, then take the service codes from each work order and render them into ordered lists as well, and finally see which work order matched up to which group.

Broken down:
This collapses the group definition.
SELECT
      WONum
    , STUFF((
             SELECT
             '| ' + dbo.WOs.ItemCode
             FROM
              dbo.WOs
             WHERE
              W.WONum = WOs.WONum
              AND ItemCode IN (
                               SELECT DISTINCT
                                  ItemCode
                               FROM
                                dbo.QualifierGroups )
                               ORDER BY
                                 ItemCode
                               FOR
                               XML PATH('')
                                ), 1, 2, '') IGroup
FROM
 dbo.WOs W
WHERE
  W.ItemCode IN ( SELECT DISTINCT
                        ItemCode
                  FROM
                  dbo.QualifierGroups )
GROUP BY
  WONum

Which results in:
 
The second half does the same thing for the groups:
SELECT
     G.GroupId
   , STUFF((
            SELECT
             '| ' + ItemCode
            FROM
              dbo.QualifierGroups
            WHERE
              GroupId = G.GroupId
            ORDER BY
              ItemCode
            FOR
              XML PATH('')
              ), 1, 2, '') IGroup
 FROM
  dbo.QualifierGroups G
 GROUP BY
  G.GroupId
Which then results in:




When put all together we get:


WITH    WOGroup
          AS (
               SELECT
                    WONum
                  , STUFF((
                            SELECT
                                    '| ' + dbo.WOs.ItemCode
                                FROM
                                    dbo.WOs
                                WHERE
                                    W.WONum = WOs.WONum
                                    AND ItemCode IN (
                                    SELECT DISTINCT
                                            ItemCode
                                        FROM
                                            dbo.QualifierGroups )
                                ORDER BY
                                    ItemCode
                          FOR
                            XML PATH('')
                          ), 1, 2, '') IGroup
                FROM
                    dbo.WOs W
                WHERE
                    W.ItemCode IN ( SELECT DISTINCT
                                            ItemCode
                                        FROM
                                            dbo.QualifierGroups )
                GROUP BY
                    WONum
             ) ,
       
        GroupCodes
          AS (
               SELECT
                    G.GroupId
                  , STUFF((
                            SELECT
                                    '| ' + ItemCode
                                FROM
                                    dbo.QualifierGroups
                                WHERE
                                    GroupId = G.GroupId
                                ORDER BY
                                    ItemCode
                          FOR
                            XML PATH('')
                          ), 1, 2, '') IGroup
                FROM
                    dbo.QualifierGroups G
                GROUP BY
                    G.GroupId
             )
SELECT
    w.WONum
    , ISNULL(gp.BonusPts, 0) BonusPts
    , g.GroupId
    , gp.GroupName
FROM
    WOGroup W
LEFT OUTER JOIN (
                    GroupCodes G
                    INNER JOIN dbo.[Group] GP
                    ON GP.GroupId = G.GroupId
                )
    ON G.IGroup = W.IGroup

So the final result becomes:




 

Note that I did add one more group (F) in addition to the others shown in the original, for testing purposes.

I hope this helps anyone who happens to stumble across it. Enjoy!
 

The Mystery of the missing Registered Servers in SSMS

I recently had an issue with my new installation of SSMS 2014. Right after I installed it, I imported my list of Registered Servers. I use this all the time to keep track of all of the several servers I manage. If you are interested. The below are two good articles about Registered Servers and why they are a great addition to your tool belt.
Good basic article:
http://sqlmag.com/sql-server/using-registered-servers-ssms
 Pretty comprehensive article with examples of ways to do multi-server scripting.
https://www.simple-talk.com/sql/sql-tools/registered-servers-and--central-management-server-stores/

So what was my problem?

I was working with my list of Registered Servers when it disappeared. Poof! Everything gone. No root or anything to work with, and no way to get it to come back.

At first this was irritating but not too bad, but the longer I went without my Registered Servers, the more I missed them.

I could find nowhere online where anyone mentioned this problem, just mentioned turning on and off the window for Registered Servers, which wasn't my problem. Finally, I felt it was worth it and started trying to do repair installs on SSMS. In the end I did multiple repair/uninstall/reinstall cycles and even upgraded my install from just SSMS 2014 to the full Developer Edition.

Nothing worked!

It turns out that the actual file that controls the Registered Servers list is stored in your profile and that mine had somehow gotten corrupted, and if you uninstall and reinstall it doesn't do anything to it!  It was not an easy file to find, though, afterwards, I saw that it is listed in the Simple Talk article, I only found that out afterwards.

So, what is this file and where is it located?
The file is RegSrvr.xml. Where it is located varies depending on your O/S (as mentioned in the Simple Talk article).
On my system running Windows 7. It is located in:
C:\Users\<login>\AppData\Roaming\Microsoft\Microsoft SQL Server\120\Tools\Shell

Yeah, it isn't grouped with
C:\Users\<login>\AppData\Local\Microsoft\SQL Server Management Studio
where most of the other settings are. That would be too easy. And since I didn't know the name of the file, I couldn't just use search to find it.

Finally, I did find it and copied over it with the one from my 2012 SSMS install which was still working. I'm still not clear how it got corrupted or why, but once I had a good copy, everything worked again.

So, I'm posting this so that if anyone else has a similar problem that they will hopefully spend less time getting it fixed.

Looking for matching sets - Looking for help

I think that this problem has to have been solved before, but I haven’t been able to find a solution on the internet.

This is a compensation problem. For work orders that contain certain combinations of items, we want to award bonus points to our workers. There are many possible combinations of work order items for which we want to assign various amounts of bonus points. We don’t want to assign bonus points at all unless a given work order contains one of the compensated combinations. 

The ultimate objective is to know what bonus points, if any should be assigned to each work order.

If someone has seen something similar, I would love to see it, or if you can find an exact solution, that would be fantastic. I’m putting the creation SQL at the end of this post, but the graphical version looks like the below.

The simplified work order information looks like this:

WORowId	WONum	ItemCode
1	1	101
2	1	300
3	2	300
4	3	300
5	3	6602
6	3	101
7	4	300
8	5	300
9	5	101
10	5	501A
11	6	101
12	7	101
13	7	300
14	8	101
15	8	300
16	8	501A
17	9	501A
18	10	8872
19	10	101
20	11	300
21	11	501A
22	11	101
23	11	101
24	11	9954B

The table that contains the groups is:

GroupId	GroupName	BonusPts
1	A	1
2	B	2.5
3	C	3.5
4	D	3.5
5	E	5

The table that specifies which combinations of item codes belongs to which qualifier is:

QualGroupId	GroupId	ItemCode
1	1	101
2	2	101
3	2	300
4	3	101
5	3	300
6	3	501A
7	4	300
8	4	501A
9	5	6602
10	5	300
11	5	101

The desired final output should be:

WONum	BonusPoints	GroupId
1	2.5	2
2	0	null
3	5	5
4	0	Null
5	3.5	3
6	1	1
7	2.5	2
8	3.5	3
9	0	Null
10	1	1
11	0 (doesn’t match because of two 101’s)	Null

Note: If a work order contains extra codes that are not in any of the lists, then they are ignored. If it contains multiples of one of the valid codes then it only matches if there is a group with the exact match.

 CREATE TABLE [dbo].[WOs](
    [WORowId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [WONum] [int] NOT NULL,
    [ItemCode] [varchar](50) NOT NULL
)
CREATE TABLE [dbo].[Group](
    [GroupId] [int] NOT NULL PRIMARY KEY,
    [GroupName] [varchar](50) NOT NULL,
    [BonusPts] [decimal](18, 2) NOT NULL
)
CREATE TABLE [dbo].[QualifierGroups](
    [QualGroupId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [GroupId] [int] NOT NULL,
    [ItemCode] [varchar](50) NOT NULL
)

SET IDENTITY_INSERT [dbo].[QualifierGroups] ON
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (1, 1, '101')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (2, 2, '101')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (3, 2, '300')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (4, 3, '101')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (5, 3, '300')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (6, 3, '501A')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (7, 4, '300')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (8, 4, '501A')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (9, 5, '6602')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (10, 5, '300')
INSERT INTO [dbo].[QualifierGroups] ([QualGroupId], [GroupId], [ItemCode]) VALUES (11, 5, '101')
SET IDENTITY_INSERT [dbo].[QualifierGroups] OFF

GO
SET IDENTITY_INSERT [dbo].[WOs] ON
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (1, 1, '101')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (2, 1, '300')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (3, 2, '300')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (4, 3, '300')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (5, 3, '6602')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (6, 3, '101')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (7, 4, '300')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (8, 5, '300')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (9, 5, '101')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (10, 5, '501A')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (11, 6, '101')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (12, 7, '101')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (13, 7, '300')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (14, 8, '101')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (15, 8, '300')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (16, 8, '501A')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (17, 9, '501A')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (18, 10, '8872')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (19, 10, '101')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (20, 11, '300')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (21, 11, '501A')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (22, 11, '101')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (23, 11, '101')
INSERT INTO [dbo].[WOs] ([WORowId], [WONum], [ItemCode]) VALUES (24, 11, '9954B')
SET IDENTITY_INSERT [dbo].[WOs] OFF


INSERT INTO [dbo].[Group] ([GroupId], [GroupName], [BonusPts]) VALUES (1, 'A', 1.00)
INSERT INTO [dbo].[Group] ([GroupId], [GroupName], [BonusPts]) VALUES (2, 'B', 2.50)
INSERT INTO [dbo].[Group] ([GroupId], [GroupName], [BonusPts]) VALUES (3, 'C', 3.50)
INSERT INTO [dbo].[Group] ([GroupId], [GroupName], [BonusPts]) VALUES (4, 'D', 3.50)
INSERT INTO [dbo].[Group] ([GroupId], [GroupName], [BonusPts]) VALUES (5, 'E', 5.00)