Match and compare two tables in SQL Server -

i need compare 2 tables in database. store similar info. in cases id , segment (substring (data, 1, 1)) match, service number , service date different each id.

here example data. in case 2 highlighted rows should selected:

i have tried this:

select      t.id,      substring(data, 1, 1) seg,      substring(data, 2, 4) sn,      convert(date, substring(data, 11, 8)),      servicenumber, servicedate     p join      t on p.id = t.id     substring(data, 1, 1) != segment     , substring(data, 2, 4) != servicenumber     , convert(date, substring(data, 11, 8)) != servicedate; 

the sql fiddle link here:

http://sqlfiddle.com/#!6/d6aee/10

any ideas more welcome!

assuming there error in pasted example, viz second row shouldn't highlighted id, segments , date same, can simplify problem breaking out encoded column components cte or derived table, , ease comparison:

with fixedtablep (      select p.id,             substring ( [data] ,1 , 1 ) psegment,             substring( [data] ,2 , 4 ) pservicenumber,             convert(date,substring ( data ,11 , 8 )) pservicedate      p ) select t.id, p.id, t.servicenumber, p.pservicenumber, t.servicedate, p.pservicedate t join fixedtablep p on p.id=t.id , p.psegment = t.segment p.pservicedate <> t.servicedate        , p.pservicenumber <> t.servicenumber; 

sqlfiddle here