Proper way of querying hierarchical data

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  March 16, 2019 at 1:35 pm

  #409705

  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: 18181

  More actions

  March 16, 2019 at 2:17 pm

  #2024705

  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: 18181

  More actions

  March 17, 2019 at 12:00 pm

  #2024727

  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

  SSCertifiable

  Points: 5181

  More actions

  March 18, 2019 at 11:44 pm

  #2024864

  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!