Get Dept Level of Hierarchy of Given ItemCode.

Hi,

ItemCode   ItemName   ItemGroup
SFD             Softdrink    
CCL            Coca Cola    SFD            
FNT             Fanta           SFD            
SPR             Sprite          SFD      
SPRZ             Sprite   Zero       SPR                    
ACL             Alcohol
TQL             Tequila        ACL
VDK             Vodka         ACL

I want to know a depth level for given ItemCode?  Given SPRZ get 3.

How could I do it?

Thank you.
LVL 1
emi_sastraAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in simple terms: with sql 2005 , you simply run a recursive query to find it, for example:
http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp
this might be the most effective if you need to know other information as well

an alternative, you could create a recursive function like this:
create function dbo.GetDepth(@ItemCode   varchar(20))
returns int
as
begin
  declare @res int
  declare @parent_group varchar(20)
  select @parent_group = ItemGroup from yourtable where itemcode = @itemcode
  set @res = @@rowcount 
  if @res > 0
    set @res = @res    dbo.GetDepth(@parent_group) 
  return @res
end

Open in new window

0
 
SharathData EngineerCommented:
try this
DECLARE @tableA table (itemCode NVARCHAR(3), itemName NVARCHAR(100), itemGroup NVARCHAR(3))
DECLARE @itemCode varchar(10)
SET @itemCode = 'SPZ' 
INSERT INTO @tableA
SELECT 'SFD', 'Softdrink', NULL
UNION ALL SELECT 'CCL', 'Coca Cola', 'SFD'
UNION ALL SELECT 'FNT', 'Fanta', 'SFD'
UNION ALL SELECT 'SPR', 'Sprite', 'SFD'
UNION ALL SELECT 'ACL', 'Alcohol', NULL
UNION ALL SELECT 'TQL', 'Tequila', 'ACL'
UNION ALL SELECT 'VDK', 'Vodka', 'ACL'
UNION ALL SELECT 'SMN', 'Smirnoff', 'VDK'
UNION ALL SELECT 'SPZ', 'Sprite Zero', 'SPR'
 
-- recursion using common table expression
;WITH cte AS (
	SELECT itemCode, itemName, itemGroup
	, 1 AS itemDepth
	FROM @tableA -- replace with your table name
	WHERE itemCode = @itemCode
	UNION ALL
	SELECT t.itemCode, t.itemName, t.itemGroup
	, c.itemDepth + 1 AS itemDepth
	FROM @tableA t
	INNER JOIN cte c ON c.itemGroup = t.itemCode) 
SELECT max(itemDepth) FROM cte

Open in new window

0
 
emi_sastraAuthor Commented:
Hi AngelIII,

I am not familiar with SP, would you please add sample data to it and show result at the SP?

Thank you.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
emi_sastraAuthor Commented:
Hi Sharat_123,

It works.

Thank you very much for your help.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
once you created my function, you use it like this with your actual table
select t.*, dbo.GetDepth(t.ItemCode) depth
  from yourtable t

Open in new window

0
 
emi_sastraAuthor Commented:
Hi AngelIII,

Ok.

Thank you very much for your 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.