Proper way of querying hierarchical data

  • kramaswamy

    SSCoach

    Points: 18135

    Hi all,

    This one's gonna be a bit tough to properly explain, so if there's anything I can provide to help clarify, please let me know. See the attached file for the test code. The situation I'm trying to solve is the following:

    I've constructed a hierarchical structure where there are essentially two types of elements: Users and Seats. In this structure, a particular entry can be one of these two types. The entry can also have a tree associated, in the case that it is a Seat - a Seat is occupied by either a User or a Seat, and for a specific term.

    My objective is to be able to query this table with a specific User ID, and return all entries which match that User ID. Another criteria is that, in the event that the User is in that table because it is occupying a Seat, the current date must lie between the Start and End date of the term.

    This is simple enough if we were to restrict the term check to just the leaf User entry. The issue is that the entire tree's term must also pass the date check.

    In the sample data structure I've constructed, the final query, where I'm passing the User ID 1 to get my output, returns the /1/ entry, as well as the /5/1/1/ entry (along with its tree, only for demo purposes). My objective is to construct the query such that the second entry is not included in the result set, due to the fact that, despite being nominally eligible due to the /5/1/1/ record having UserID 1 and a term with today's date between Start and End date of the term, the /5/1/ record's Start and End date do not. IE, the result set should just contain Record ID 1, and not Record ID 9.
  • kramaswamy

    SSCoach

    Points: 18135

    After thinking it over, I've come up with one possible solution - though I'm certainly still open to others, as well as commentary on my proposed solution. Essentially what I'm doing is, for each row I want to test, I check to see how many elements higher up in the tree (including itself) need to be tested, and, how many elements higher up in the tree (including itself) have at least one Term entry with valid dates. If those two are equal, then the record becomes valid:


    SELECT
     targetOccupant.ID,
     targetOccupant.Hierarchy.ToString(),
     targetOccupant.OccupantTypeID,
     targetOccupant.ItemID,
     AncestorTermCount,
     ValidTermCount
    FROM #Occupant targetOccupant
    OUTER APPLY
    (
     SELECT COUNT(*) As AncestorTermCount, ISNULL(SUM(ValidTermCount), 0) AS ValidTermCount
     FROM #Occupant occupantAncestry
     OUTER APPLY
     (
      SELECT MAX(1) AS ValidTermCount
      FROM #Term
      WHERE #Term.OccupantID = occupantAncestry.ID
       AND GETDATE() BETWEEN StartDate AND EndDate
     ) o2
     WHERE targetOccupant.Hierarchy.IsDescendantOf(occupantAncestry.Hierarchy) = 1
      AND occupantAncestry.Hierarchy.GetAncestor(1) != @Root
    ) o1
    WHERE targetOccupant.ItemID = 1
     AND targetOccupant.OccupantTypeID = 1
     AND AncestorTermCount = ValidTermCount

  • kramaswamy

    SSCoach

    Points: 18135

    Another update. After thinking about this some more, I've realised that the problem boils down to taking a given node, and finding if the tree going upwards to the root passes whatever test criteria is being used at each level. The total number of successful tests, then, must equal the level of the node being tested. So here's a new stab at the same problem:

    SELECT
     targetOccupant.ID,
     targetOccupant.Hierarchy.ToString(),
     targetOccupant.OccupantTypeID,
     targetOccupant.ItemID,
     targetOccupant.Hierarchy.GetLevel() AS HierarchyLevel,
     ValidTermCount
    FROM #Occupant targetOccupant
    CROSS APPLY
    (
     SELECT SUM(ISNULL(ValidTermCount, 0)) AS ValidTermCount
     FROM #Occupant occupantAncestry
     OUTER APPLY
     (
      -- Test whether the given parent node passes the test criteria
      SELECT MAX(1) AS ValidTermCount
      FROM #Term
      WHERE #Term.OccupantID = occupantAncestry.ID
       AND GETDATE() BETWEEN StartDate AND EndDate
     ) validParent
     WHERE targetOccupant.Hierarchy.IsDescendantOf(occupantAncestry.Hierarchy) = 1
    ) parentTree
    WHERE targetOccupant.ItemID = 1
     AND targetOccupant.OccupantTypeID = 1
     -- -1 because of the special case in this example, that the second level is a seat without the possibility of having a term to test
     AND targetOccupant.Hierarchy.GetLevel() - 1 = ValidTermCount

  • handkot

    SSCarpal Tunnel

    Points: 4501

    my solution.
    it's not good, but it can help you

    ;
    With h As (
     Select
      o.Hierarchy
      , o.ItemID
      , StartDate = IsNull(t.StartDate, '19000101')
      , EndDate = IsNull(t.EndDate, '30000101')
      , o.ID
      , o.OccupantTypeID
    --  , ot.Name
     From
      #Occupant o
     Left Join #Term t On t.OccupantID = o.ID
    -- Left Join #OccupantType ot On ot.ID = o.OccupantTypeID
     ),
     res As (
      Select
       h.*
       , cnt = Count(*) Over (Partition By h.ID)
       , sm = Sum(Case When GetDate() BetWeen hp.StartDate And hp.EndDate Then 1 Else 0 End) Over (Partition By h.ID)
      From
       h h
      Join h hp On h.Hierarchy.IsDescendantOf(hp.Hierarchy) = 1
     )
    Select
     r.ID
     , r.ItemID
     , r.StartDate
     , r.EndDate
     , r.Hierarchy.ToString()
    From
     res r
    Where
     r.ItemID = 1
     And r.OccupantTypeID = 1
     And r.sm = r.cnt

    I Have Nine Lives You Have One Only
    THINK!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply