Solved

Find matching set of records

Posted on 2014-11-03
20
78 Views
Last Modified: 2014-11-11
I have the following tables on SQL Server 2008R2

MessageTable

ContrlNo|    LineNo   |    Msg
-------|--------------|--------------
1        |        1        |    Tiger1 Text
1        |        2        |    Tiger1 Text
1        |        3        |    Tiger1 Text
1        |        4        |    Tiger1 Text
2        |        1        |    Tiger1 Text1
2        |        2        |    Tiger1 Text2
2        |        3        |    Tiger1 Text3
2        |        4        |    Tiger1 Text4
3        |        1        |    Horse 1
3        |        2        |    Horse 2
3        |        3        |    Horse 3
3        |        4        |    Horse 4

RuleTable

RuleNo|    MsgLineNo  | RuleStartingPos |    RuleMsg
-------|--------------|-----|---------
1        |        1        |  1 |  Tiger1 Text
2        |        1        |  1 |  Tiger1 Text
2        |        3        |  1 |  Tiger1 Text3

For each set of ControlNo records in the MESSAGETABLE I would like to apply the rule from the RULETABLE and list the RULENo if any mataches.

For the above example, I would like to have the following output,

ContrlNo | RuleNo
-----------------
1   |   1 <br>
2   |   2 <br>
3   |   NULL

Thanks,
Jay
0
Comment
Question by:rationalJay
  • 10
  • 8
20 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40419793
SELECT DISTINCT m.ContrlNo, r.RuleNo
FROM MessageTable m
LEFT OUTER JOIN RuleTable r ON m.ContrlNo = r.RuleNo

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40419804
I think this code is right, or at least very close, but I haven't tested it as I don't have directly useable test data available:


SELECT message_Contrlno.ContrlNo, rule_matched.RuleNo
FROM (
    SELECT DISTINCT ContrlNo
    FROM MessageTable
) AS message_Contrlno
CROSS APPLY (
    SELECT TOP (1) rt.RuleNo, mt.ContrlNo
    FROM dbo.RuleTable rt
    LEFT OUTER JOIN MessageTable mt ON
        rt.MsgLineNo = mt.[LineNo] AND
        rt.RuleMsg = mt.Msg
    WHERE mt.ContrlNo = message_Contrlno.ContrlNo
    GROUP BY rt.RuleNo
    HAVING COUNT(rt.MsgLineNo) = COUNT(mt.Msg)
    ORDER BY COUNT(rt.MsgLineNo) DESC
) AS rule_matched
ORDER BY ContrlNo
0
 
LVL 1

Author Comment

by:rationalJay
ID: 40419969
Thanks for you quick response.  I tried your query

SELECT distinct message_Contrlno.ContrlNo, rule_matched.RuleNo
FROM (
    SELECT ContrlNo
    FROM MessageTable
) AS message_Contrlno
CROSS APPLY (
    SELECT rt.RuleNo
    FROM dbo.RuleTable rt
    LEFT OUTER JOIN MessageTable mt ON
        rt.MsgLineNo = mt.[LineNo] AND
        rt.RuleMsg = SUBSTRING(mt.Msg,rt.RuleStartingPos,LEN(rt.RuleMsg))
    WHERE mt.ContrlNo = message_Contrlno.ContrlNo
    GROUP BY rt.RuleNo
    HAVING COUNT(rt.MsgLineNo) = COUNT(mt.Msg)
       
) AS rule_matched
ORDER BY ContrlNo

I have attached the the result  as an image. I do not get the expected result as shown below,

ContrlNo | RuleNo
-----------------
1   |   1 <br>
2   |   2 <br>
3   |   NULL

Thanks,
Jay
1.PNG
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40420121
No, you re-wrote my query.  My query was:

SELECT message_Contrlno.ContrlNo, rule_matched.RuleNo
FROM (
     SELECT DISTINCT ContrlNo
...

My query will not give duplicate results for ContrlNo, yours will.
0
 
LVL 1

Author Comment

by:rationalJay
ID: 40420158
That's right, Sorry I messed it up little bit. Your query yields following result,

ContrlNo|      RuleNo
-------------|------------------
1              |            2
2              |            2

But, that is not the intended result.

If you see the RuleTable, the Rule 2 overlaps rule 1. And the requirement is to get the most matched RuleNo for each Control number. the expected result is,

ContrlNo|      RuleNo
-------------|------------------
1              |            1
2              |            2

-Thanks...
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40420216
Ok, I'll accept that.

Again, without testable data, I won't be able to fix it.  That takes running the query to test.
0
 
LVL 1

Author Comment

by:rationalJay
ID: 40420457
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40420541
Sorry, that was off, please try code below.  I believe it will work, although we'll have to see about performance.  Sorry about the issue with INSERT statements, but it usually takes me longer to convert plain text into INSERT statements than it does to write the query!  [And I simply don't have time to do that.]


SELECT message_Contrlno.ContrlNo, rule_matched.RuleNo
FROM (
    SELECT DISTINCT ContrlNo
    FROM MessageTable
) AS message_Contrlno
LEFT OUTER JOIN (
    SELECT rt.RuleNo, mt.ContrlNo,
        ROW_NUMBER() OVER(PARTITION BY rt.RuleNo ORDER BY COUNT(rt.MsgLineNo), mt.ContrlNo DESC) AS row_num
    FROM dbo.RuleTable rt
    LEFT OUTER JOIN MessageTable mt ON
        rt.MsgLineNo = mt.[LineNo] AND
        rt.RuleMsg = mt.Msg
    GROUP BY rt.RuleNo, mt.ContrlNo
) AS rule_matched ON
    rule_matched.ContrlNo = message_Contrlno.ContrlNo AND
    rule_matched.row_num = 1
ORDER BY ContrlNo
0
 
LVL 1

Author Comment

by:rationalJay
ID: 40421492
Thanks....
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:rationalJay
ID: 40421588
Each control Number should have just one rule mapped to it. When I added one more rule your latest query failed....

http://sqlfiddle.com/#!3/bda20/1
0
 
LVL 1

Author Comment

by:rationalJay
ID: 40421879
And the following lines just compare MessageTable record against the last row of the RuleNo data set. The approach is right but It should compare all the rows in decending fashion.

 rt.MsgLineNo = mt.[LineNo] AND
 rt.RuleMsg = mt.Msg

Open in new window


Thanks,
Jay
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 500 total points
ID: 40421900
SELECT message_Contrlno.ContrlNo, rule_matched.RuleNo
FROM (
    SELECT DISTINCT ContrlNo
    FROM MessageTable
) AS message_Contrlno
LEFT OUTER JOIN (
    SELECT rt.RuleNo, mt.ContrlNo, --COUNT(rt.MsgLineNo) AS rules_matched,
        ROW_NUMBER() OVER(PARTITION BY mt.ContrlNo ORDER BY COUNT(rt.MsgLineNo) DESC, rt.RuleNo) AS row_num
    FROM dbo.RuleTable rt
    LEFT OUTER JOIN MessageTable mt ON
        rt.MsgLineNo = mt.[LineNo] AND
        rt.RuleMsg = mt.Msg
    GROUP BY rt.RuleNo, mt.ContrlNo
) AS rule_matched ON
    rule_matched.ContrlNo = message_Contrlno.ContrlNo AND
    rule_matched.row_num = 1
ORDER BY ContrlNo
0
 
LVL 1

Author Comment

by:rationalJay
ID: 40421953
@Scott, I believe you have not seen my last comment,

http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28549689.html#a40421879
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40422046
That is just to join every row that needs considered.  The ROW_NUMBER() and other things take care of the other requirements, as I understand them.

Keep in  mind, you know more in depth how these rules work, I don't.  I can go only by what you post, including the examples you gave, which were extremely limited.

If you can give a sample data set and the correct/expected result, which my query isn't giving, then I can debug it.
0
 
LVL 1

Author Comment

by:rationalJay
ID: 40422403
First, Thanks for helping. I totally understand your difficulty.  I hope the following example explains it better,

http://sqlfiddle.com/#!3/20c5d/1


When we run the query we have, we will get from the example that ContrlNo 5 matches RuleNo 2. But actually it should not. it should match RuleNo 6. But other ContrlNos have perfect match.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40422575
That link's not working.

But run the inner subquery stand-alone, and check the results that way, including a column for # of rules matched (which, btw, we could pass through to the final result if you wanted to):

    SELECT rt.RuleNo, mt.ContrlNo, COUNT(rt.MsgLineNo) AS rules_matched,
         ROW_NUMBER() OVER(PARTITION BY mt.ContrlNo ORDER BY COUNT(rt.MsgLineNo) DESC, rt.RuleNo) AS row_num
     FROM dbo.RuleTable rt
     LEFT OUTER JOIN MessageTable mt ON
         rt.MsgLineNo = mt.[LineNo] AND
         rt.RuleMsg = mt.Msg
     GROUP BY rt.RuleNo, mt.ContrlNo
0
 
LVL 1

Accepted Solution

by:
rationalJay earned 0 total points
ID: 40422681
Scott, Thank you so much for your patience and support. What we had was very close... The following  produces exactly what I wanted....

SELECT MT.ContrlNo, r.RuleNo, r.MatchPercent
FROM
  MessageTable MT
   LEFT JOIN
    (
      SELECT
        ContrlNo,
        RuleNo,
        MatchedRules / AvailableRules AS MatchPercent,
        ROW_NUMBER() OVER (PARTITION BY ContrlNo ORDER BY MatchedRules DESC, MatchedRules / AvailableRules DESC) AS rn
      FROM
        (
          SELECT
            ContrlNo,
            R.RuleNo,
            COUNT(*) as MatchedRules,
            (SELECT COUNT(*) FROM RuleTable WHERE RuleTable.RuleNo = R.RuleNo) + 0.0 AS AvailableRules
          FROM
              MessageTable M
               INNER JOIN
              RuleTable R ON
                  M.[LineNo] = R.MsgLineNo AND
                  SUBSTRING(M.Msg,R.RuleStartingPos,LEN(R.RuleMsg)) LIKE '%' + R.RuleMsg + '%'
          GROUP BY M.ContrlNo, R.RuleNo
        ) q
     ) r ON
    MT.ContrlNo = r.ContrlNo AND
    r.rn = 1
GROUP BY MT.ContrlNo, r.RuleNo, r.MatchPercent
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40422690
Oh, OK, not the most rules matched (by count) but the highest % rules matched -- that makes sense.
0
 
LVL 1

Author Closing Comment

by:rationalJay
ID: 40434580
help from scottPletcher got me close to the solution. But the last post from me has the exact solution I wanted.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now