Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Looping - Indeterminate Counts

Status
Not open for further replies.

firepwr

IS-IT--Management
May 22, 2006
31
US
We have a department like many with a director and multiple layers of supervisors. Some employees may be only one layer away from the director while others may be multiple layers away.

For various reasons we're trying to pull a list of all of our employees from a much larger enterprise list (without a department field) by starting with the director, getting his direct reports, then getting their direct reports, on down the line until we have everyone including our non-supervisory employees. I've been trying to do this in a cursor, however, the problem is that some "threads" have one level while other more compartmentalized groups have multiple levels. I can't seem to figure out how to code for this indeterminate number of iterations depending on which group it is....

Has anyone ever done something similar or have any suggestions or---even better---code snippets? I would think it would be a similar process to what you would need to do to generate an Org Chart on-the-fly.... Thanks!
 
There really isn't an easy way to do this. It will require a loop. Something like this I've used in the past.

Code:
declare @Emp table
(EmployeeID int)

insert into @Emp
select [i]Director's Employee Number[/i]

while @@ROWCOUNT <> 0
BEGIN
    insert into @Emp
    select EmployeeID
    from EmployeeTable
    Where SupervisorID in (select EmployeeID from @Emp)
    and EmployeeID not in (select EmployeeID from @Emp)
END

select *
from EmployeeTable
Where EmployeeID in (select EmployeeID from @Emp)

Another option would be wait for SQL 2008 and take a look at the hierarchyid data type ( about half way down).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Denny, is there a reason you're using IN () and NOT IN () syntax instead of inner joins and left joins? I think the joins are more efficient.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
No.

Just lazy and this is the way that I've always written this perticular script.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Also the NOT IN is needed to prevent the loop from running for ever.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
In SQL 2005 you can use Common Table Expressions.

For 2000, here's some looping code that I've done. For this particular one I needed to be able to get various trees all at once, trees that didn't necessarily have any overlap. The tree table also has the hierarchy list so I was able to do some cheating. Note that the techniques I use here may not be useful in your situation. I did testing among 11 or so different versions to find the most efficient way with my particular database and data and all that. But I don't have time to build you something custom.

Code:
CREATE FUNCTION TreeByCategoryList (@CategoryList as varchar(8000))
RETURNS @OrderLeafItems TABLE (
   ItemID numeric(16,0),
   OrderItemID numeric(16,0),
   Depth int,
   Category varchar(255)
)
AS
BEGIN
   -- Split @CategoryList into rows in @Categories table
   DECLARE @Categories TABLE (
      Category varchar(255)
   )

   DECLARE
      @Pos int,
      @End int

   SET @Pos = 1

   SET @CategoryList = @CategoryList + '|'
   SET @End = CharIndex('|', @CategoryList)
   WHILE @End > 0 BEGIN
      INSERT @Categories SELECT SubString(@CategoryList, @Pos, @End - @Pos)
      SET @Pos = @End + 1
      SET @End = CharIndex('|', @CategoryList, @Pos)
   END

   DECLARE @Tree TABLE (
      ItemID numeric(16,0),
      ParentID numeric(16,0),
      OrderItemID numeric(16,0),
      Hierarchy varchar(255),
      [Name] varchar(255)
   )

   DECLARE @Depth int
   SET @Depth = 0

   INSERT @Tree (ItemID, ParentID, OrderItemID, Hierarchy, [Name])
   SELECT DISTINCT
      ItemID,
      0, -- won't care about ParentID for items with no selected ancestors
      OrderItemID,
      Hierarchy, -- necessary in order to remove items in list that have ancestors in list, so as not to have duplicates
      [Name]
   FROM
      dbo.OrderItemList I
      INNER JOIN @Categories C ON I.Name = C.Category
   WHERE
      Active = 1

   -- now remove items that have ancestors in the list
   DELETE T
   FROM
      @Tree T
      INNER JOIN @Tree X ON '|' + T.Hierarchy + '|' LIKE '%|' + Convert(varchar(16), X.ItemID) + '|%'

   INSERT @Tree (ItemID, ParentID, OrderItemID, [Name])
   SELECT
      I.ItemID,
      I.ParentID,
      I.OrderItemID,
      I.[Name]
   FROM
      dbo.OrderItemList I
      INNER JOIN @Tree T ON
         '|' + I.Hierarchy + '|' LIKE '%|' + Convert(varchar(16), T.ItemID) + '|%'
   WHERE
      Active = 1

   INSERT @OrderLeafItems (ItemID, OrderItemID, Depth, Category)
   SELECT
      ItemID,
      OrderItemID,
      1,
      [Name]
   FROM @Tree
   WHERE ParentID = 0

   WHILE @@RowCount > 0 BEGIN
      SET @Depth = @Depth + 1

      INSERT @OrderLeafItems (ItemID, OrderItemID, Depth, Category)
      SELECT
         T1.ItemID,
         T1.OrderItemID,
         @Depth + 1,
         T2.Category
      FROM
         @Tree T1
         INNER JOIN @OrderLeafItems T2 ON T1.ParentID = T2.ItemID
      WHERE T2.Depth = @Depth 
   END

   DELETE @OrderLeafItems WHERE OrderItemID = 0

   RETURN
END

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top