Finding Hierarchy - Single table

[mossbs]

Hi Guys,

Got an issue I cant seem to figure out...

I have one table that contains what part numbers are currently assigned in each country (table CurrentPart - columns Part_Number nvarchar(50), Country_Code nvarchar(2))

then another table that contains data about part replacements, structure as follows...

CREATE TABLE PartReplacement](
	[Old_Part_Number] [nvarchar](50) NULL,
	[New_Part_Number] [nvarchar](50) NULL,
	[Country_Code] [nvarchar](2) NULL,
	[Indicator] [int]
)

In the part replacement table - you could have Part A replaced by Part B
(Old_Part_Number = 'A' New_Part_Number = 'B')
...but then also part B could've been replaced by Part C
(so Old_Part_Number = 'B' New_Part_Number = 'C')
this could continue on for a fair few repetitions.

What I need to do is be able to report on the full hierarchy of the replacements, with the starting point being whatever the part list is from the CurrentPart table (column Part_Number)

... so for example..

CurrentPart..Part_Number = 'A'
CurrentPart..Country_Code = 'UK'

then in the PartReplacement table the succession of part replacements in countrycode 'UK' goes from 'A' through to part 'E' being the last part...


so i would need to show somthing like

Country    OldPart       PartReplacements   NewPart
UK         A             A,B,C,D,E          E

Is this possible?!

Cheers in advance - any help greatly appreciated.

 

[simian336]

Sure... Can you post some test data from both tables.

Simi

 

[mossbs]

cheers Simi...

small amount of test data here...

insert into PartReplacement
select 
'0102124','0102299','UK',1
union select
'0102299','0102254','UK',1
union select
'0102254','0101940','UK',1
union select
'0102124','0102299','DE',1
union select
'0102299','0102254','DE',1
union select
'0102254','0101940','DE',1
union select
'0101940','0043904','DE',1
union select
'1715148','1767209','SX',1
union select
'1715148','1767209','TR',1
union select
'1715148','1767209','FR',1
union select
'1715148','1767209','IT',1


insert into CurrentPart
select 
'0102299','UK'
union select
'0102124','DE'
union select
'1715148','SX'
union select
'1715148','TR'
union select
'1715148','FR'
union select
'1767209','IT'

tried to give a few scenarios with the test data -
Some longer replacement chains - with different countries starting at different points in it.
then some smaller chains across countries where the last one (country = 'IT') - shouldnt be returned as the current part is the most up to date one... (ie not present in the 'Old_Part_Number' column)

Think that makes sense!?

Cheers mate.

 

[drlex]

I'd probably use recursion here.

Example solution which requires a null 'new part' entry for current codes and ignores indicator field

with PartReplacements ([Old_Part_Number], [New_Part_Number], [Country_Code], [PartList]) as
(
    select parentPartReplacement.[Old_Part_Number],
           parentPartReplacement.[New_Part_Number],
           parentPartReplacement.[Country_Code],
           cast(parentPartReplacement.[Old_Part_Number] as varchar(50))
    from   PartReplacement parentPartReplacement
    where  [New_Part_Number] is null
    union all
    select childPartReplacement.[Old_Part_Number],
           childPartReplacement.[New_Part_Number],
           childPartReplacement.[Country_Code],
           cast(childPartReplacement.[Old_Part_Number] + ', ' +  PartReplacements.PartList  as varchar(50))
    from   PartReplacement childPartReplacement
    join   PartReplacements
    on     childPartReplacement.[New_Part_Number] = PartReplacements.[Old_Part_Number] and
childPartReplacement.[Country_Code] = PartReplacements.[Country_Code]
)

select [Country_Code], [Old_Part_Number], PartList, RIGHT(Partlist,1) as [Latest_Part_Number] from PartReplacements
order by PartList

Data set used:

CREATE TABLE [PartReplacement](
	[Old_Part_Number] [nvarchar](50) NULL,
	[New_Part_Number] [nvarchar](50) NULL,
	[Country_Code] [nvarchar](2) NULL,
	[Indicator] [int]
) 

Insert Into [PartReplacement] Values('A','B','UK',1)
Insert Into [PartReplacement] Values('B','C','UK',1)
Insert Into [PartReplacement] Values('C','D','UK',1)
Insert Into [PartReplacement] Values('D','E','UK',1)
Insert Into [PartReplacement] Values('E',NULL,'UK',1)

output:

Country	Old    PartList	     Latest_Part_Number
UK	A	A, B, C, D, E    E
UK	B	B, C, D, E       E
UK	C	C, D, E	         E
UK	D	D, E	         E
UK	E	E	         E

soi là, soi carré

 

[simian336]

Did Drlex's example work for you... It would require a larger dataset then you provided...

Simi

 

[mossbs]

Cheers dudes, having a play with Drlex's example at the minute... made a couple of edits as there wont be a NULL value in the table... if part hasn't been replaced then it just doesn't appear in the Old_Part_Number column.

Looks like its doing the trick though!

Muchos thanks man!

 

[simian336]

I was playing with it.. figured since I had it done I would post the manual way to do it. Thought someone might be interested.

Simi


--drop table Hierarchy
Create table Hierarchy (Country_Code nvarchar(2),
[Level1] [nvarchar](50) NULL,
[Level2] [nvarchar](50) NULL,
[Level3] [nvarchar](50) NULL,
[Level4] [nvarchar](50) NULL,
[Level5] [nvarchar](50) NULL,
[Partlist] [nvarchar](500) NULL,
Latest_Part [nvarchar](50) NULL)

--delete Hierarchy
insert into Hierarchy (level1, Country_Code)
select Part_Number, Country_Code from CurrentPart

Update h
set level2 = new_part_number
from Hierarchy h
left join PartReplacement p1
on h.level1=p1.old_part_number
and h.country_code = p1.country_code

Update h
set level3 = new_part_number
from Hierarchy h
left join PartReplacement p1
on h.level2=p1.old_part_number
and h.country_code = p1.country_code

Update h
set level4 = new_part_number
from Hierarchy h
left join PartReplacement p1
on h.level3=p1.old_part_number
and h.country_code = p1.country_code

Update h
set level5 = new_part_number
from Hierarchy h
left join PartReplacement p1
on h.level4=p1.old_part_number
and h.country_code = p1.country_code

--********************************************************

Update h
set Partlist = level1
,Latest_Part= level1
from Hierarchy h

Update h
set Partlist = Partlist + ', ' + Level2
,Latest_Part= level2
from Hierarchy h
where Level2 is not null

Update h
set Partlist = Partlist + ', ' + Level3
,Latest_Part= level3
from Hierarchy h
where Level3 is not null

Update h
set Partlist = Partlist + ', ' + Level4
,Latest_Part= level4
from Hierarchy h
where Level4 is not null

Update h
set Partlist = Partlist + ', ' + Level5
,Latest_Part= level5
from Hierarchy h
where Level5 is not null

select *
from Hierarchy

 

[mossbs]

Hi guys, apologies for slow reply... (been on A/L for week... so just come back to it... )

Have managed to get the recursive CTE working...

with PartReplacements ([Old_Part_Number], [New_Part_Number], [Country_Code], [PartList]) as
(
    select [Old_Service_Part_Number],
           [New_Service_Part_Number],
           Market_Code,
           cast(Old_Service_Part_Number+', '+New_Service_Part_Number as varchar(1000))
    from   PartReplacement
    where  New_Service_Part_Number not in (select Old_Service_Part_Number from PartReplacement)
		   and OSI_Indicator !=4
		   
    union all
    select Old_Service_Part_Number,
           New_Service_Part_Number,
           Market_Code,
           cast(Old_Service_Part_Number+ ', ' + PartList as varchar(1000))
    from   PartReplacement
    join   PartReplacements
    on     New_Service_Part_Number = Old_Part_Number and (Market_Code = [Country_Code])
	where OSI_Indicator !=4
)

select 
[Country_Code], 
[Old_Part_Number], 
PartList, 
right(Partlist,7) as [Latest_Part_Number] 
from PartReplacements

However... I now have a further issue I can't seem to figure out...there's another table: PartPrice, that contains prices for the parts. (columns: PartNumber, Country, Price).. however some Parts may not be in there meaning they don't have prices.

join from PartReplacement to PartPrice is on

PartReplacement.New_Service_Part_Number = PartPrice.PartNumber
and PartReplacement.Market_Code = PartPrice.Country

What I need to happen is that the 'Replacement Chain' reports to the latest part that has a price.

Couple of scenarios using my earlier 'PartNumber = A replaced by B then C through to E' example...

Ex1 -
A to E ...all are priced = report all the way and 'Latest Part' E is reported on with its price

Ex2 -
D and E aren't priced = report on chain up to C with price for C

Ex3 -
D isnt priced but E is = report full chain to E showing price for E


So basically it always reports as far down the chain as possible to provide the most recent part that has a price...

Does that make sense? and is it possible to use this Recursive CTE method?

Cheers guys