Solved

How can I match most specific to least specific records in SQL?

Posted on 2013-06-16
6
469 Views
Last Modified: 2013-06-21
Here is the back story.

I am trying to generate a list of transactions that includes the correct commission value for a specific person.  Not everyone is eligible for commission, but those who are have entries in a commission table that specifies what invoice item, item subclass, item class, and/or default  commission value they earn.

My problem is in the way the commission table is constructed that makes it very difficult to select on as shown below:

**COMMISSION Table**

    staffid sequence invoiceitemid subclassid invclassid commission commtype 
    ------- -------- ------------- ---------- ---------- ---------- -------- 
    KH      1        (null)        (null)     (null)     20.0       1        
    KH      2        (null)        (null)     BOA        0.0        2        
    KH      3        (null)        (null)     GRO        0.0        2        
    KH      4        (null)        (null)     HEA        5.0        2        
    KH      5        (null)        (null)     FTP        5.0        2        
    KH      6        (null)        (null)     NTR        0.0        2        
    KH      7        (null)        EUK        NTR        5.0        2        
    KH      8        (null)        FOP        NTR        5.0        2        
    KH      9        (null)        PUR        NTR        5.0        2        
    KH      10       (null)        RC         NTR        5.0        2        
    KH      11       (null)        (null)     MSC        0.0        2        
    KH      12       (null)        (null)     MIS        0.0        2        
    KH      13       1171          (null)     (null)     15.8       2        
    KH      14       1173          (null)     (null)     15.2       2        

Open in new window



Again, only one commission value should be selected per transaction based on the most specific to least specific columns in the COMMISSION table.

**Most Specific-** invoiceitemid, subclassid, invclassid -**Least Specific**

For example 1:  
If the transaction included:

 - invoiceitemid: 1234
 - subclassid: FOP
 - invclass: NTR

The SQL should match on the subclassid ("FOP") and not on the invclass ("NTR") resulting in a commission of 5.0 instead of 0.0

For example 2:
If the transaction included no matches:

 - invoiceitemid: 1234
 - subclassid: STR
 - invclass: BLA

The SQL should match on the invoiceitemid (null), subclassid (null), and invclass (null) resulting in a commission of 20.0

**What I've Tried**
Using COALESCE(invoiceitemid, subclassid, invclassid) to create a column with the most specific item in that record works.

However, in the first example, NTR occurs before FOP in the table, so it is incorrectly matched upon first. (See Below)

    SELECT COALESCE(invoiceitemid, subclassid, invclassid) AS COMBINE, * 
    FROM stcomm
    WHERE staffid = 'KH'
    ORDER BY sequence DESC

    COMBINE staffid sequence invoiceitemid subclassid invclassid commission commtype 
    ------- ------- -------- ------------- ---------- ---------- ---------- -------- 
    URN     KH      28       (null)        URN        MSC        0.0        2        
    6326    KH      27       6326          (null)     (null)     0.0        2        
    6325    KH      26       6325          (null)     (null)     0.0        2        
    6324    KH      25       6324          (null)     (null)     0.0        2        
    6328    KH      24       6328          (null)     (null)     0.0        2        
    5671    KH      23       5671          (null)     (null)     20.0       2        
    5793    KH      22       5793          (null)     (null)     20.0       2        
    4263    KH      21       4263          (null)     (null)     0.0        2        
    5081    KH      20       5081          (null)     (null)     20.0       2        
    3759    KH      19       3759          (null)     (null)     0.0        2        
    4846    KH      18       4846          (null)     (null)     0.0        2        
    SUH     KH      17       (null)        SUH        SUP        5.0        2        
    SUD     KH      16       (null)        SUD        SUP        5.0        2        
    SUP     KH      15       (null)        (null)     SUP        0.0        2        
    1173    KH      14       1173          (null)     (null)     15.2       2        
    1171    KH      13       1171          (null)     (null)     15.8       2 

Open in new window



To fix this issue, I ordered the sequence field in descending so the most specific "FOP" would be selected first.  This does work.

**How do I query the COMMISSION table to show the correct commission in my transaction?**

Here is my SQL for my transaction:

    SELECT T.staffid, T.invoiceid, T.invoiceitemid, I.subclassid, I.classid, T.quantity, T.invoiceprice
    FROM TRANSACT T, INVOITEM I
    WHERE T.invoiceitemid = I.invoiceitemid
    AND T.staffid = 'KH'

    staffid invoiceid invoiceitemid subclassid classid quantity invoiceprice 
    ------- --------- ------------- ---------- ------- -------- ------------ 
    KH      2555.0    04000         FOP        NTR     2.00     3.40         
    KH      3575.0    04000         FOP        NTR     7.00     11.90        
    KH      3981.0    04000         FOP        NTR     6.00     10.20        
    KH      4333.0    04000         FOP        NTR     1.00     1.79         
    KH      6401.0    04000         FOP        NTR     5.00     8.95         
    KH      7863.0    04000         FOP        NTR     12.00    21.48        
    

Open in new window



I have never encountered a table with such a strange design before with no primary key to select on.

For any assistance you guys can provide, I would be very grateful!
0
Comment
Question by:Johnny_SQL
  • 4
  • 2
6 Comments
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
Comment Utility
It's been a few years since I've written SQL and I've done this in 2 steps.. it can probably be combined into one statement, and my syntax for NULL might not be correct, but I'm sure you can see what I'm doing.  This might be what you need or close to what you need. After these 2 steps, add a query to match tempSeq.sequence to commission.sequence for the other fields from commision.

SELECT T.staffid, T.invoiceid, T.invoiceitemid, I.subclassid, I.classid, T.quantity, T.invoiceprice
    FROM TRANSACT T, INVOITEM I
    WHERE T.invoiceitemid = I.invoiceitemid
    AND T.staffid = 'KH' INTO table tempx

SELECT t.*,MIN(c.sequence) FROM tempx t, commission c
  WHERE t.invoiceitemid=c.invoiceitemid  OR ;
    (c.invoiceitemid=NULL AND t.subclassid=c.subclassid AND t.classid = c.invclassid) OR (c.invoiceitemid=NULL AND t.subclassid=c.subclassid AND c.invclassid=NULL) OR (c.invoiceitemid=NULL AND c.subclassid=NULL AND t.classid = c.invclassid) OR (c.invoiceitemid=NULL AND c.subclassid=NULL AND c.invclassid=NUL  
GROUP BY 1,2,3,4,5,6,7
INTO TABLE tempSeq
0
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
Comment Utility
It probably would be faster to just grab invoiceID, invoiceitemid, subclassid and classid in the first query, that way you only have to group on one field (invoiceID) in the second query. The thrid query can query all 4 tables to grab the rest of the fields you need.
0
 

Author Comment

by:Johnny_SQL
Comment Utility
I have come up with the following SQL, but have an issue I am trying to resolve.

SELECT T.staffid, T.invoiceid, T.quantity, T.invoiceprice, T.invoiceitemid, I.subclassid, I.classid, C.sequence
FROM TRANSACT T, INVOITEM I, stcomm C
WHERE T.invoiceitemid = I.invoiceitemid
AND T.staffid = 'KH'
AND T.staffid = C.staffid
AND T.invoiceid = 2410
AND     (I.invoiceitemid = C.invoiceitemid
        OR (C.invoiceitemid IS NULL AND I.subclassid=C.subclassid AND I.classid = C.invclassid)
        OR (C.invoiceitemid IS NULL AND C.subclassid IS NULL AND C.invclassid = I.classid)
        OR (C.invoiceitemid IS NULL AND C.subclassid IS NULL AND C.invclassid IS NULL))

Open in new window


The problem is that it is first matching on the default commission (ie.  null, null, null for invoiceitemid, subclassid, invclassid) and then matching again on the more specific item.

staffid invoiceid quantity invoiceprice invoiceitemid subclassid classid sequence 
------- --------- -------- ------------ ------------- ---------- ------- -------- 
KH      2410.0    1.00     0.00         2970          EXM        PRS     1        
KH      2410.0    1.00     16.00        2793          VSC        VAS     1        
KH      2410.0    1.00     10.00        1101          (null)     VAS     1        
KH      2410.0    1.00     8.00         4797          (null)     LAB     1        
KH      2410.0    1.00     30.00        3414          (null)     LAB     1        
KH      2410.0    1.00     0.00         RTG           (null)     MSC     1        
KH      2410.0    1.00     0.00         RTG           (null)     MSC     11       
KH      2410.0    1.00     22.50        1171          VSC        VAS     1        
KH      2410.0    1.00     22.50        1171          VSC        VAS     13       

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Johnny_SQL
Comment Utility
Using two subqueries in the main select, I think I have the solution, but I am still testing.

Candidate Solution:
SELECT T.staffid, T.invoiceid, T.quantity, T.invoiceprice, T.invoiceitemid, I.subclassid, I.classid,
        (
        SELECT MAX(sequence)
        FROM stcomm C
        WHERE T.staffid = C.staffid
        AND (I.invoiceitemid = C.invoiceitemid
        OR (C.invoiceitemid IS NULL AND C.subclassid IS NULL AND C.invclassid IS NULL)
        OR (C.invoiceitemid IS NULL AND C.subclassid IS NULL AND C.invclassid = I.classid)
        OR (C.invoiceitemid IS NULL AND I.subclassid=C.subclassid AND I.classid = C.invclassid))
        ) AS MYsequence,
        (
        SELECT commission
        FROM stcomm D
        WHERE T.staffid = D.staffid
        AND D.sequence = MYsequence
        ) AS commission
FROM TRANSACT T, INVOITEM I
WHERE T.invoiceitemid = I.invoiceitemid
AND T.staffid = 'KH'
AND T.invoiceid = 2410

Open in new window

0
 

Accepted Solution

by:
Johnny_SQL earned 0 total points
Comment Utility
Here is another way of writing it with the subquery nested into the where of the main subquery:

SELECT T.staffid, T.invoiceid, T.quantity, T.invoiceprice, T.invoiceitemid, I.subclassid, I.classid,
        (
        SELECT commission
        FROM stcomm D
        WHERE T.staffid = D.staffid
        AND D.sequence = 
                (SELECT MAX(sequence)
                FROM stcomm C
                WHERE T.staffid = C.staffid
                AND (I.invoiceitemid = C.invoiceitemid
                OR (C.invoiceitemid IS NULL AND C.subclassid IS NULL AND C.invclassid IS NULL)
                OR (C.invoiceitemid IS NULL AND C.subclassid IS NULL AND C.invclassid = I.classid)
                OR (C.invoiceitemid IS NULL AND I.subclassid=C.subclassid AND I.classid = C.invclassid))
                )
        ) AS commission
FROM TRANSACT T, INVOITEM I
WHERE T.invoiceitemid = I.invoiceitemid
AND T.staffid = 'KH'
AND T.invoiceid = 2410

Open in new window

0
 

Author Closing Comment

by:Johnny_SQL
Comment Utility
This solution works perfectly, but there may be a more efficient way of writing it.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

772 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

10 Experts available now in Live!

Get 1:1 Help Now