Combined Item number and serial number to be unique value

I have a report that sends out a DB mail alert when duplicate serial numbers are detected across company databases, and it works fine 99 present of the time, however we have had a couple of situations where it detects a duplicate serial number condition, but in those cases they weren't actually duplicates as they had the same serial number but different Item numbers. My code is looking at strictly serial numbers but apparently that is not 100 Percent accurate, so what I would like to do is ignore the results if the serial number is the same but the Item number is different.

This is the view (SSG_AD_AllSerials) that is called by the view that generates the report
SELECT        SERLNMBR, ITEMNMBR, LOCNCODE, DEX_ROW_ID, 'AMCHR' AS Company
FROM            dbo.IV00200
WHERE        SERLNMBR <> 'NONE'
UNION ALL
SELECT        SERLNMBR, ITEMNMBR, LOCNCODE, DEX_ROW_ID, 'ATI' AS Company
FROM            ATI.dbo.IV00200
WHERE        SERLNMBR <> 'NONE'
UNION ALL
SELECT        SERLNMBR, ITEMNMBR, LOCNCODE, DEX_ROW_ID, 'ATIMF' AS Company
FROM            ATIMF.dbo.IV00200
WHERE        SERLNMBR <> 'NONE'

Open in new window

   

View that generates the report
SELECT        TOP (100) PERCENT SERLNMBR AS Serial_No, ITEMNMBR AS Item_No, LOCNCODE AS Site, Company
FROM            dbo.SSG_AD_AllSerials
WHERE        (SERLNMBR IN
                             (SELECT        SERLNMBR
                               FROM            dbo.SSG_AD_AllSerials AS SSG_AD_AllSerials_1
                               GROUP BY SERLNMBR
                               HAVING         (COUNT(*) <> 1))) 
ORDER BY Serial_No

Open in new window

skull52Asked:
Who is Participating?
 
Kyle AbrahamsSenior .Net DeveloperCommented:
A simple concatenation should do it for you:

SELECT        TOP (100) PERCENT SERLNMBR AS Serial_No, ITEMNMBR AS Item_No, LOCNCODE AS Site, Company
FROM            dbo.SSG_AD_AllSerials
WHERE        (SERLNMBR + '-' + ITEMNMBR IN
                             (SELECT        SERLNMBR + '-' + ITEMNMBR 
                               FROM            dbo.SSG_AD_AllSerials AS SSG_AD_AllSerials_1
                               GROUP BY SERLNMBR + '-' + ITEMNMBR 
                               HAVING         (COUNT(*) <> 1))) 
ORDER BY Serial_No

Open in new window

0
 
Scott PletcherSenior DBACommented:
Concatenation might hurt performance.  You could also join to the dups:


SELECT        ssa.SERLNMBR AS Serial_No, ssa.ITEMNMBR AS Item_No, ssa.LOCNCODE AS Site, ssa.Company
FROM            dbo.SSG_AD_AllSerials saa
INNER JOIN (
                 SELECT        SERLNMBR, ITEMNMBR
                 FROM            dbo.SSG_AD_AllSerials AS SSG_AD_AllSerials_1
                 GROUP BY  SERLNMBR, ITEMNMBR
                 HAVING        (COUNT(*) > 1)
           ) AS dup_serials ON dup_serials.SERLNMBR = saa.SERLNUMBR AND dup_serials.ITEMNMBR = saa.ITEMNMBR
                 
ORDER BY ssa.Serial_No
0
 
skull52Author Commented:
Scott,
that generates the following errors

Msg 207, Level 16, State 1, Line 8
Invalid column name 'SERLNUMBR'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ssa.SERLNMBR" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ssa.ITEMNMBR" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ssa.LOCNCODE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ssa.Company" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "ssa.Serial_No" could not be bound.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
skull52Author Commented:
Scott,
Also performance is not really an issues as this is part of a SQL job that runs in the middle of the night when no one is on the system.
0
 
Scott PletcherSenior DBACommented:
Should be 'saa.' instead of 'ssa.'
0
 
skull52Author Commented:
Yeah I missed that one...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
skull52, do you still need help with this question?
0
 
skull52Author Commented:
Thanks to all for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.