August 14, 2008 at 8:02 am
Hello,
I have 2 tables:
Table A:
Material Maint_status
02R2149 KDVCB
02R2152 KDVCB
02R2154 KDVCB
02R2166 KCVDB
02R2174 KCVDB
Table B:
Material Maint_status
02R2149 KDCBV
02R2152 KDVCB
02R2154 KDCB
02R2166 KCVDB
02R2174 VDK
1) Maint_status field contains only characters (K, C, V, D, B) but not necessary in any order (scramble).
2) I want to find out all the entries in Table B which does not match Table A and the missing character
The Result:
Material Maint_status Missing Characters
02R2154 KDCB V
02R2174 VDK CB
Is this possible to get this result with a Select Statement ?
Thanks in Advance,
Manosh Majumdar
August 14, 2008 at 8:33 am
Questions:
1. Can the characters (K, C, V, D, B) be in any order in both tables?
2. should we worry about records in Table A that have no matching Material value in Table B?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2008 at 8:36 am
Thanks for the response.
1) Yes, Characters can be in any order in both tables.
2) We are not worried about missing records in Table A. We need to search only Table B.
August 14, 2008 at 8:54 am
This should work:
Select * From (
Select A.Material, A.Maint_status,
CASE When CharIndex(Substring(A.Maint_status,1,1), B.Maint_status) > 0
Then ''
Else Substring(A.Maint_status,1,1) End
+CASE When CharIndex(Substring(A.Maint_status,2,1), B.Maint_status) > 0
Then ''
Else Substring(A.Maint_status,2,1) End
+CASE When CharIndex(Substring(A.Maint_status,3,1), B.Maint_status) > 0
Then ''
Else Substring(A.Maint_status,3,1) End
+CASE When CharIndex(Substring(A.Maint_status,4,1), B.Maint_status) > 0
Then ''
Else Substring(A.Maint_status,4,1) End
+CASE When CharIndex(Substring(A.Maint_status,5,1), B.Maint_status) > 0
Then ''
Else Substring(A.Maint_status,5,1) End
As [Missing Characters]
From TableA as A
Join TableB as B ON A.Material = B.Material) as R
Where [Missing Characters] > ''
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2008 at 9:07 am
Wow... Working... thanks...
August 14, 2008 at 9:08 am
One of the problems with non-normalized solutions is you always have to figure out how to use it ...
So first normalize, then use, time and again ...
This solution uses Jeff Modens Tally table ...
and gives a "normalized" solution.
Create table #TbA ( Material char(7) not null primary key , Maint_Status char(5) not null)
--
Insert into #TbA
select '02R2149', 'KDVCB'
union all
select '02R2152', 'KDVCB'
union all
select '02R2154', 'KDVCB'
union all
select '02R2166', 'KCVDB'
union all
select '02R2174', 'KCVDB'
Create table #TbB( Material char(7) not null primary key , Maint_Status char(5) not null)
Insert into #TbB
select '02R2149', 'KDCBV'
union all
select '02R2152', 'KDVCB'
union all
select '02R2154', 'KDCB'
union all
select '02R2166', 'KCVDB'
union all
select '02R2174', 'VDK'
Select * from #tbA order by Material
Select * from #tbB order by Material
--===========================================================================================================
-- Demonstrate the solution
--===========================================================================================================
;WITH
cteSplitA AS
(--==== Split or "Normalize" the whole table at once
SELECT Material,
SUBSTRING(dt.Maint_Status,N,1) AS Registered_Status
FROM tempdb.dbo.Tally t
CROSS JOIN #TbA dt
WHERE N <= DATALENGTH(dt.Maint_Status) )
, cteSplitB AS
(--==== Split or "Normalize" the whole table at once
SELECT Material,
SUBSTRING(dt.Maint_Status,N,1) AS Registered_Status
FROM tempdb.dbo.Tally t
CROSS JOIN #TbB dt
WHERE N <= DATALENGTH(dt.Maint_Status)
)
--===== List the missing items
SELECT distinct T.*, A.Registered_Status as Missing_Status
FROM #TbB T
inner join cteSplitA A
on T.Material = A.Material
left join cteSplitB B
on A.Material = B.Material
and A.Registered_Status = B.Registered_Status
where B.Material is null
order by T.Material
drop table #TbA
drop table #TbB
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply