Solved

Identify simular records from a table.

Posted on 2011-03-02
15
192 Views
Last Modified: 2012-05-11
I hope I can explain this correctly. I have a table that has records of items. There are two types of items Base items and not base items. the not based items are created from the Base items. The diference is in the identifacation code. Base Items have a Code like 123-456. While all other items would have the same code but the dash is replaced with the letter A (123A456). Each record has a defect code. when the Base item is changed, the non based item also needs to be changed. Lots of times the others are not changed. So I would like to create a query or Stored Procedure to identfy these records. Here are some examples:
Example 1
Item             Identity Code        Defect Code
Item1             123-456                 A
Item1             123Y456               A

Nothing needs to be done to Example 1
Example 2  
Item             Identity Code        Defect Code
Item2             123-555                 B
Item2             123Y555               A

Here the second record needs the defect code to B. But what I need is a query to identify the records that need to change along with its base record. So the two records in example 2 would display.  I'm not quite sure how to do this. I can use functions, temp variable tables or whatever is needed to get this to work.  Thanks in advance.
0
Comment
Question by:MadIce
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 35022171
probably something like
select item, replace([identity code], 'Y', '-') as [Identity], [Defect code] from yourtable order by [Identity]
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35022188
or

select item, [identity code], [Defect code] from yourtable order by replace([identity code], 'Y', '-')
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35022247
Try this:

Select i1.*
  from #items i1
  inner join #items i2 on i2.Item = i1.Item
 group by i1.Item, i1.[Identity Code], i1.[Defect Code]
 having COUNT(DISTINCT i2.[Defect Code]) > 1
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35022365
Actually I think this is what you're after


select 	a.item, 
	a.[identity code], 
	(select top 1 [Defect code]) from yourtable where item = a.item order by [identity code])
from yourtable a
order by a.item

Open in new window

0
 

Author Comment

by:MadIce
ID: 35022568
I trying all the examples. ralmada: the last example seems close but I need to clarify something. Each item can have 1 record or a hundred records. The Identitycode makes it unique. So I not sure if top 1 will work.  It partialy works.
wdosanjos:  I thought this example would work but not getting any return records.
I'm trying to modify both examples to get one of them to work.

0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35022635
I think the actual data may have some scenario that the sample you provided does not address. Can you provide your query?
0
 

Author Comment

by:MadIce
ID: 35022714
wdosanjos: Here is what I have from your example
Select i1.Item, i1.SN, i1.IDCode, i1.DCode
  from DTStatus i1
  inner join DTStatus i2 on i2.Item = i1.Item and i2.SN = i2.SN
 group by i1.Item, i1.SN, i1.IDCode, i1.DCode
 having COUNT(DISTINCT i2.DCode) > 1
order by i1.Item, i1.SN, i1.IDCode

I do get records now. I was using my ID field on the join and changed it to above.  What makes a record unique is the Item, SN and IDCode. So for item1 there is 390 unique records. Most are Base records but a few are other than base.  
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 40

Expert Comment

by:Sharath
ID: 35022773
Ifpossible, create a function to extract only numbers.
create function dbo.OnlyNumbers(@String varchar(500))
returns int as
begin
declare @result varchar(100)
    
    SET @result=''
 SELECT @result = @result + CASE WHEN number LIKE '[0-9]' THEN number ELSE '' END 
   FROM (SELECT SUBSTRING(@String,number,1) AS number 
           FROM (SELECT number FROM master..spt_values 
                  WHERE type = 'p' AND number BETWEEN 1 AND LEN(@String)) as t) as t 
 return convert(int,@result)
end

Open in new window

Use this function in your code.
select t1.*
  from your_table t1
  join (
select Item
  from your_table 
 group by Item,dbo.OnlyNumbers(Identity_Code)
having MAX(Defect_Code) <> MIN(Defect_Code)) t2 on t1.Item = t2.Item

Open in new window

Tested with your sample data.
use Sharath
create table #table(Item varchar(10),Identity_Code varchar(10),Defect_Code varchar(1))
insert #table values('Item1',             '123-456',               'A')
insert #table values('Item1',             '123Y456',               'A')
insert #table values('Item2',             '123-555',               'B')
insert #table values('Item2',             '123Y555',               'A')

select t1.*
  from #table t1
  join (
select Item
  from #table 
 group by Item,dbo.OnlyNumbers(Identity_Code)
having MAX(Defect_Code) <> MIN(Defect_Code)) t2 on t1.Item = t2.Item
/*
Item	Identity_Code	Defect_Code
Item2	123-555	B
Item2	123Y555	A
*/
drop table #table

Open in new window

0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35022841
I'm not sure how your data is but I think you should just join by Item not Item £ SN.
0
 

Author Comment

by:MadIce
ID: 35022850
Sharath_123:  I was trying to figure out a way to use a function. I was trying to refer to it in the select.  I will try this out. I might not be able to respone back till tomorrow.
0
 

Author Comment

by:MadIce
ID: 35023029
Sharath_123: I've mislead you with my example. there can be characters in the beginning and end also. its the position of the - or Y that needs to be checked. So an Identity_Code could be AB1-23T or AB1Y23T.  But still, I think I can get your example to work. Otherwise I have another idea to try out.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35023090
Ok. For non-based items, the hyphen is replaced with any letter (A-Z) or is it always Y?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35023530
>>I trying all the examples. ralmada: the last example seems close but I need to clarify something. Each item can have 1 record or a hundred records. The Identitycode makes it unique. So I not sure if top 1 will work.  It partialy works.
<<

When you say it partially works, what do you mean by that? Can you be more specific?
Also if you provide some sample data and the expected result that will be better.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35024229
try this

will work in sql 2005,2008

which version are you using?


;with cte as (
select x.*
      ,case when idcode like '%-%' then 'Base' else 'NB' end as [Type]
      ,left(idcode,charindex('-',idcode+'-')-1) as p1
     ,stuff(idcode,1,charindex('-',rtrim(idcode)+'-'),'') as p2
  from dtstatus as x
) 
--select * from cte

,cte2 as (
select b.idcode as baseid,b.[dcode] as d,nb.*
   from cte as B
   inner Join cte as NB
     on b.item=nb.item     
     and b.sn=nb.sn
    and B.type='Base'
   --    and nb.type='NB'  
    and nb.idcode like b.p1+'_'+b.p2 
    )
--    select * from cte2
    
    , cte3 as (    
    select item,sn,baseid
      from cte2
     where d<>[dcode]
         )
   select a.item,a.idcode,a.[dcode]
     from cte2 as a
     inner join cte3 as b
     on a.item=b.item 
     and a.sn=b.sn
     and a.baseid=b.baseid
     order by a.item,a.baseid,a.Type,a.idcode,a.[dcode]

Open in new window

0
 

Author Closing Comment

by:MadIce
ID: 35030568
That worked great. I made some mods to make it work for me like using substring with len because I'm looking at the position but it works better than expected because I can query results different ways Like showing both base  and nb or just nb records.  Because of where I work, at can't post data or examples that would help. That's why I try to create examples. One thing I left out is a - can be used more than once in the id. Its the position that matters for me. Thanks so much.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

708 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

14 Experts available now in Live!

Get 1:1 Help Now