January 16, 2014 at 3:39 pm
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.
Full SQL and example posted here:
Thanks in advance,
Dave
January 16, 2014 at 4:02 pm
Could you post Full SQL and example here? I can't access to blogspot sites.
January 16, 2014 at 5:47 pm
I almost have an answer for you, but order number 11 confuses me...why does it not match GroupId 4 (D)?
with GroupData as
(
-- Gather the groups and item codes together and count how many items in each group
select G.GroupId, G.GroupName, G.BonusPts, Q.ItemCode,count(*) over(partition by G.GroupId) as HowManyInGroup
from [Group] G
join QualifierGroups Q
on Q.GroupId = G.GroupId
),
WorksData AS
(
-- Now bring in the works orders matched on ItemCode and count how many ItemCode(s) match
-- and also how many distinct ItemCode(s) are in the matching works order/group combination
-- for an exact match, you need both counts to equal HowManyInGroup
select G.GroupId, G.GroupName, G.BonusPts, G.HowManyInGroup, W.WONum
, count(*) as WOCount
, count(distinct W.ItemCode) as WOItemCount
from GroupData G
join WOs W
on W.ItemCode = G.ItemCode
group by G.GroupId, G.GroupName, G.BonusPts, G.HowManyInGroup, W.WONum
),
WoRanked AS
(
-- Add a row number so we can take the match with the most ItemCode(s) as the answer for each WONum
-- and filter down to exact matches using the three counts
select W.GroupId, W.GroupName, W.BonusPts, W.HowManyInGroup, W.WoNum, W.WoCount, W.WoItemCount
, row_number() over(partition by W.WoNum order by W.WoCount desc) as WORank
from WorksData W
where W.HowManyInGroup = W.WOCount and W.WOCount = W.WOItemCount
)
-- select the best result for each WONum
select W.WoNum, WR.BonusPts, WR.GroupId
from WoRanked WR
right outer join (
select distinct W.WONum from WOs W
) W
on W.WONum = WR.WONum
where WoRank = 1
or WR.WONum is null
order by W.WoNum
Of course, if you have a lot of data to process, you might need to Divide'n'Conquer by using temporary tables for the intermediate results instead of a combination of CTEs - for performance.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 17, 2014 at 1:12 am
I'm sure that this can be improved upon but it was fun while it lasted.
WITH DupItems AS
(
SELECT WONum, ItemCnt=MAX(ItemCnt)
FROM
(
SELECT WONum, ItemCnt=COUNT(*)
FROM dbo.[WOs]
GROUP BY WONum, ItemCode
) a
GROUP BY WONum
)
SELECT b.WONum, [BonusPts]=ISNULL([BonusPts], 0)
,GroupID=CASE WHEN ISNULL(c2, 1) = 1 THEN a.GroupID ELSE NULL END
FROM
(
SELECT WONum, GroupID, ITems, c1
,c2=(SELECT ItemCnt FROM DupItems c WHERE a.WONum = c.WONum)
FROM
(
SELECT WONum, GroupID, ITems
,c1=MAX(Items) OVER (PARTITION BY WONum)
FROM
(
SELECT a.WONum, GroupID, Items=COUNT(*)
FROM dbo.[WOs] a
CROSS APPLY
(
SELECT *
FROM QualifierGroups b
WHERE a.ItemCode = b.ItemCode
) b
GROUP BY a.WONum, GroupID
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.[QualifierGroups] c WHERE b.GroupID = c.GroupID)
) a
) a
) a
RIGHT JOIN
(
SELECT WONum
FROM dbo.[WOs]
GROUP BY WONum
) b ON a.WONum = b.WONum
FULL JOIN dbo.[Group] c ON CASE WHEN ISNULL(c2, 1) = 1 THEN a.GroupID ELSE NULL END = c.GroupID
WHERE c1=Items OR c1 IS NULL
ORDER BY b.WONum, GroupID;
Like Mister Magoo, I was confused about WO 11, which you say should have GroupID=NULL because it has a duplicate 101. Is that because there's any duplicate item in it or because the duplicate item appears within a chosen group? I chose to implement the former.
Unless I'm mistaken, this is relational division with a nasty twist! :w00t:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 17, 2014 at 6:54 am
One thing that it seems I wasn't clear about. In the example WO #11 doesn't match, not because there are duplicate codes, groups that contain duplicates are valid. So, if a group contains "101, 101, 300" it should only match a work order with 101 twice and a single 300. Conversely if a work order only has a single 101 and 300 it should only match a group with a single 101 and 300, but should not match groups with multiples. It is entirely valid for a group to contain, "101, 101, 300, 300, 501A, 501A, 501A" and it should only match with work orders that contain all those items in those quantities.
That is one of the trickier parts of this.
Thanks to everyone with your solutions so far.
For those who can't see my blog post, here is the SQL that I used to create the examples:
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)
Also, the desired output should be along these lines:
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
January 17, 2014 at 8:09 am
David, I still don't understand 11 not matching 4.
Group 4 has two ItemCodes, WOnum 11 has those two codes ???
What is the logic behind it not matching group 4?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 17, 2014 at 8:18 am
MM,
Thanks for your help.
The problem is that a given work order can't just have the codes at all, but must match quantity as well.
So WO #11 has the 101 code twice. The group only has it once. That's why it doesn't match.
I failed to put it in the sample data but, as I mentioned in my earlier reply, there are groups that contain multiples of the same code.
So, WO #11 would match against a group that contained "101,101,300,501A" but it doesn't/shouldn't match against any of the other groups.
I hope that clears things up.
Thanks again,
Dave
January 17, 2014 at 9:00 am
DavidKBennett (1/17/2014)
MM,Thanks for your help.
The problem is that a given work order can't just have the codes at all, but must match quantity as well.
So WO #11 has the 101 code twice. The group only has it once. That's why it doesn't match.
I failed to put it in the sample data but, as I mentioned in my earlier reply, there are groups that contain multiples of the same code.
So, WO #11 would match against a group that contained "101,101,300,501A" but it doesn't/shouldn't match against any of the other groups.
I hope that clears things up.
Thanks again,
Dave
But works order 11 matches group 4 exactly - it is made up of 300 and 501A only, and Order 11 has exactly one of each of those Items.
My code matches this.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 17, 2014 at 9:38 am
MM,
WO #11 does have those codes, but it also has two 101s. That is the problem.
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')
Try adding this to see what I meant above.
INSERT INTO [dbo].[Group] ([GroupId], [GroupName], [BonusPts]) VALUES (6, 'F', 15.00)
INSERT INTO [dbo].[QualifierGroups] ([GroupId], [ItemCode]) VALUES ( 6, '101'),(6,'101'),(6,'300'),(6,'501A')
Hopefully, that will make my conundrum clear.
Thanks again,
Dave
January 17, 2014 at 9:49 am
Sorry, it is no clearer I'm afraid, unless ...
Are you saying that any works order that has :
more than one occurrence of any single ItemCode, <-- in this case 101
and
that ItemCode appears in a Group <-- 101 appears in Groups 1,2,3 and 5
and
that ItemCode appears fewer times in any Group than it does in the order <-- 101 appears once only in Groups 1,2,3 and 5 but is on order 11 twice
cannot match any groups at all?
Is that it?
I am trying to get you to express the logic that excludes it from the one to one match it has with group 4.
Adding more groups doesn't help with that problem...at least I don't see how it does...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 17, 2014 at 9:57 am
Are you saying that any works order that has :
more than one occurrence of any single ItemCode, <-- in this case 101
and
that ItemCode appears in a Group <-- 101 appears in Groups 1,2,3 and 5
and
that ItemCode appears fewer times in any Group than it does in the order <-- 101 appears once only in Groups 1,2,3 and 5 but is on order 11 twice
cannot match any groups at all?
That is it exactly.
My code from the last reply was to add a group that contained multiple occurrences of the same code, since that wasn't in my original sample code.
January 17, 2014 at 4:51 pm
Here is a revised version that gives the correct results I think.
with GroupData as
(
-- Gather the groups and item codes together and count how many items in each group
select G.GroupId, G.GroupName, G.BonusPts, Q.ItemCode,count(*) over(partition by G.GroupId) as HowManyInGroup
from [Group] G
join QualifierGroups Q
on Q.GroupId = G.GroupId
),
WorksData AS
(
-- Now bring in the works orders matched on ItemCode and count how many ItemCode(s) match
-- and also how many distinct ItemCode(s) are in the matching works order/group combination
-- for an exact match, you need both counts to equal HowManyInGroup
select G.GroupId, G.GroupName, G.BonusPts, G.HowManyInGroup, W.WONum
, count(*) as WOCount
, count(distinct W.ItemCode) as WOItemCount
, count(W.ItemCode) as WOItemsAllCount
from GroupData G
join WOs W
on W.ItemCode = G.ItemCode
group by G.GroupId, G.GroupName, G.BonusPts, G.HowManyInGroup, W.WONum
),
CheckForInvalidGroups as
(
select W.GroupId, W.GroupName, W.BonusPts, W.HowManyInGroup, W.WoNum, W.WoCount, W.WoItemCount
, count(case when WoItemsAllCount>WoItemCount then 1 end) over(partition by WoNum) HasInvalidGroups
from WorksData W
),
WoRanked AS
(
-- Add a row number so we can take the match with the most ItemCode(s) as the answer for each WONum
-- and filter down to exact matches using the three counts
select W.GroupId, W.GroupName, W.BonusPts, W.WoNum
, row_number() over(partition by W.WoNum order by W.WoCount desc) as WORank
from CheckForInvalidGroups W
where W.HowManyInGroup = W.WOCount and W.WOCount = W.WOItemCount
and W.HasInvalidGroups = 0
)
-- select the best result for each WONum
select W.WoNum, ISNULL(WR.BonusPts,0), WR.GroupId, WR.GroupName
from WoRanked WR
right outer join (
select distinct W.WONum from WOs W
) W
on W.WONum = WR.WONum
where WR.WoRank = 1
or WR.WONum is null
order by W.WoNum
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 20, 2014 at 3:03 pm
MM,
Thanks again for your efforts.
When I tested your latest solution against my enhanced group (from my previous reply), it didn't work.
I instead went a completely different way, which while not really relational or very elegant, seems to handle all of my various edge cases.
I am basically collapsing down my various combinations of rows into a single value per group and per work order and then comparing them. This seems to handle all variations of numbers of codes.
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, g.GroupId, gp.GroupName, gp.BonusPts
FROM WOGroup W
LEFT OUTER JOIN ( GroupCodes G
INNER JOIN dbo.[Group] GP
ON GP.GroupId = G.GroupId
)
ON G.IGroup = W.IGroup
It seems a bit brute force, but it does look to get the job done.
If you can come up with a fully relational solution that handles all the possible permutations I would love to see it.
In the mean time this does seem to work.
Thanks,
Dave
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply