Displaying Hierarchical Data

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4805

    Comments posted to this topic are about the item Displaying Hierarchical Data

  • Jeff Moden

    SSC Guru

    Points: 994951

    There is no need to make another scan on the table in the outer SELECT just to get things like the ManagerName.

    Also, if the CEO want's to be listed at the top, she should be included as the root node at the beginning of the return.  I suspect that she should also have her own department but that's another story.

    Here's the code that covers both of the points from above.


       WITH HierarchyList_CTE AS
    (
     SELECT  StaffID, StaffName, ManagerID, Department
            ,ManagerName = StaffName
            ,StaffLevel = 1
       FROM Reference.Staff
      WHERE ManagerID IS NULL
      UNION ALL
     SELECT  tbl.StaffID, tbl.StaffName, tbl.ManagerID, tbl.Department
            ,ManagerName = cte.StaffName
            ,StaffLevel = cte.StaffLevel + 1
       FROM Reference.Staff tbl
       JOIN HierarchyList_CTE cte
         ON tbl.ManagerID = cte.StaffID
    )
     SELECT Department, StaffName, ManagerName, StaffLevel
       FROM HierarchyList_CTE
    ;

    I could be wrong about the requirements but I also suspect that she wants to see the hierarchical order of direct reports in a ,manner similar to what you have in Figure 1.  The addition of a "SortPath" using a bit of concatenation will handle that nicely.  The SortPath in the following is based on the StaffID.


       WITH HierarchyList_CTE AS
    (
     SELECT  StaffID, StaffName, ManagerID, Department
            ,ManagerName = StaffName
            ,StaffLevel = 1
            ,SortPath = CONVERT(VARBINARY(4000),CONVERT(BINARY(4),StaffID))
       FROM Reference.Staff
      WHERE ManagerID IS NULL
      UNION ALL
     SELECT  tbl.StaffID, tbl.StaffName, tbl.ManagerID, tbl.Department
            ,ManagerName = cte.StaffName
            ,StaffLevel = cte.StaffLevel + 1
            ,SortPath = CONVERT(VARBINARY(4000),cte.SortPath + CONVERT(BINARY(4),tbl.StaffID))
       FROM Reference.Staff tbl
       JOIN HierarchyList_CTE cte
         ON tbl.ManagerID = cte.StaffID
    )
     SELECT Department, StaffName, ManagerName, StaffLevel
       FROM HierarchyList_CTE
      ORDER BY SortPath
    ;

    If the sort needs to be by name within each set of names in the hierarchy and still maintain the hierarchical structure, it will be less efficient but the following will do such a thing...


       WITH HierarchyList_CTE AS
    (
     SELECT  StaffID, StaffName, ManagerID, Department
            ,ManagerName = StaffName
            ,StaffLevel = 1
            ,SortPath = CONVERT(VARCHAR(8000),StaffName)
       FROM Reference.Staff
      WHERE ManagerID IS NULL
      UNION ALL
     SELECT  tbl.StaffID, tbl.StaffName, tbl.ManagerID, tbl.Department
            ,ManagerName = cte.StaffName
            ,StaffLevel = cte.StaffLevel + 1
            ,SortPath = CONVERT(VARCHAR(8000),cte.SortPath + tbl.StaffName)
       FROM Reference.Staff tbl
       JOIN HierarchyList_CTE cte
         ON tbl.ManagerID = cte.StaffID
    )
     SELECT Department, StaffName, ManagerName, StaffLevel
       FROM HierarchyList_CTE
      ORDER BY SortPath
    ;

    Here are the results from that code.
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAb4AAAE0CAYAAABTplZXAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAEr4SURBVHhe7b1/jBxnet/50DBsHOBccMDdHz6Tw12he26hG3EJS0GkISXtCILl7jF9Q4QcJaGkwS7CHivgHZsHTCivBtAOMA7CG+RmxuZBnhaczSyXl3DEgJPI020ailYSOU050SZjsj1YTzd2JZJ3vsPmgsReA7d72fDe533f6nqruruqeqqqu6r7+xFesbverurp93nrfX68b73PgW984xuPTk39KgEAAADDwM/ofwEAAIChoMXj+++//Nf1K2DxJ3/8R/oVAACAtONQfKz0MMi3cq74Bl1e+Qf6HQAAgDTTVHxQep25vvkH9NZbb+l3oFvQrwAASQKKLwCs+D788EN4ffsEHjMAIEl0pfh++49+Vr9qz3e/q1+4WP+7/0m/Sideiu/f/Yf/V/7753/+5/Jfk3//7/+cnjqa0e+GF8tjhmEFAEgCXa/qfPLJJ+0i/hNHmsVRp8ug86d/+gv0/PPP04kTJxzl6aefpuqP/1J/CkxMTOhXAADQX7pWfN8Vbl2ziP/EEecxV5Ef8eLBNXrl6NM05ioLt3V9WPj6r12j+/ptnLDX993/R/xkUd7/7Of8fzsAAICe05XiY/9trfBks5z95Ufy30/XVPmHf/tHzdfbvz1GokpQ4v95c+QClXc+oZpV3rtA9VLEyipmBfi9732P6vU6PfrBp7T3/f9If/FvG7oGAABAkgj1APtf/qUzlGfNc/34xz+Whfm5n7sn/+2KQyOUvfsZfa7fJp2S8GxZ6bGD+yd/8qf0k93fp5//+e/p2n1gecGLd/QB5iFdeU0c65H3CgAAg0pXio8Hc/ZsPv30U/roo4/kMf7XKkyp9F36wz/8Q7px4z35fl88uE/1I1+gw/K1GQo9S1ceyE/Q/atnm2HRV64+bPXoHO8/o2++uUw7d5cpr4+1nB+CwpNPynm9gnBxZ2bOyPJzj/8anT37y3T3J1+i7f/0s7L8F3/lv9FnBODIE3R0777xe7bp5l39GgAAwL7p2uP70pe+RE899ZRc0MHwv1b5sz/7b+lv/s2sfD32a2eCez2skLQSkuXEZzT7rZdphL2cN9+nl96zQqAv0s03WXHdoW8uZehtHRalpat0S1+qPV+gr/7WBTrKIVV53W7P9+bhw4f0wx/+UBb2+v7Jv/6P8ji309/6a39VKkae75uevtSF8nuRXhp9nz7Sip4+/4zo1Ek6qt8CAADYH10pvh//+Ev6VSsc2vzFX/w/5ev6//dfyn8D45jju04Xj9ygD3hxi/Ry7tGlE5ZCZK+NlcEhekx85nX21j4+Rt/euUjPqisFJOz5Tn7yk5/Q7u6uLD/60Uf0C//Xx/QXP6Pn+EolUWbly9/93bNdKb/nv5Khmx8rb/TWhw166StfkK8lQT3hDsduLVrnqtJcTOS4riiW19zF9wEAQJLpSvH99Kf/Sq7UtEKbf/oLv0DvvfdeM7zJHt8//ad1qv3+VVl+vK/l/Afp1cJJevdDa37rpPLMmuUdevWQ+My31PvZH5wSg+6lLj22sOc7eeyxxxyeL4c9mxQKoqzRiy/+V/Qb77wjD7HyC8RhoehubksP94O9F+l5GftluvGE23u3z87rc/VxtZiIr7tM2cv2ceVhRuV5AwBA/+k61MnP5lkDPIfvfuVXfqUZ3uTQ5pN/40kaG1NzXD/96U+FN/SEPrMLjj9Hp69/TLcOHaOXLO+PkV6HUFLyX+V1PDvPHmKDPiPXgpjPP6Md/bKFdudbIcV9Yj3KIMOd4t8m7O3Nzkrl+OST3xV6UCjCoI838u8Xv+zz2x/Tu6Mj4hdquvKEO3i3pgfH51vXpQv01eP8xiA2zxsAAHpP14rPhEOb1urNZnjzM6K9/6zmuPaPGlA/uM2e2TLROWvAZa9DDK6HXqa/P0d6ID5FN196S3iBz9BX5xpyEJafLTWc82FypSjPJQrF2fZ8/bkQjPznH9L9n9FhTOsZPuHt0dqaVIwXX9xQSs+q8+UgPf9Sg9bEbzn9lWf0MYugnnC7Y3doQbalPrfp2XkRh+cNAAC9p2vFxyFOK7xphTZ5BSeHNhkOhf6HT/93WQIhlNC35YITEzWgviU9j2foLcdgKz9AI2fe0cc+oW+fOdhyrPatd9R1m9e3rqO8knbnh4ENgKbSE8q/ifD2aPYpqfguvT+tDwZn5LkXie5m6AXTC+vGE77dybvN0BcsZW95x9LDXKZvujcP6Ob7QnrOAAAQN10rvt/8zd+khYXfp3/8j/9HofxK8t/f+Z2PhVPzv8nCx65cudIs+3qOL80IpVeip+wVrcLb++FihX70ox/R3/7v/mEX3p6GFXdLCLELT/h4AO/43A37ur91gerN6+oQaDffF4HnDAAAcYLsDAHw2qR6+49+ln75lx/Rv/k3Bxxzdz9f+558no+9PebTs79Lv/HOb1DhyTVpHKz/3tfk8URz+xKNffgc1ebdYdbuQHYLAECSCDXHBxS8WrNa/R2p0Kxi8U/+zr+kUmFNKkZWeqF2dImdO7RgeYHSE2zQxZlwSg8AAJIGPL4A7Mdj+XSnIUPAnUiFxxcR8PgAAEkCHl9McB4+Vm6dCgAAgP4Ajy8Alsfyne98Rx8BAACQVqD4AAAADBUdFR97OQAAAMCg4an4OLwHAAAADBK+ii/t81oTExOYm0sAkEO8oH2TD2SUDFgOgRRfWuf+3Mo7rb8j7UAO8YL2TT6QUTKw5IDHGcBwYWalkMW1sbZfPQBpJ5X3gGtzDb1H8H6B4gNDBGelWCaau25vZu7YB9WvHoC0k9Z7wEhWECibjDfDrfhaLBtRrIzjSUH+jeGsm1D07PvdFh0X43uj+Dse3Kc6PUEvPdchG0fHevW3NbPUS/hYGrxB3a6Ofq2PLVrJntNOmuVjouXiKAm5B/RY6Wzj9AKPT3Dayji+c50u0jLlk6b8Bp6HdOW1C1R3WJlc3qLn9SeSwLvnUhz2NJM0c2Jj/XKQSLV8UnIPDApQfA5UWp6jd5056e5fPWtYYMbN1bSCuNO2sdAEznM7nU90a9H+jLKqtPUnUwMZ2c9FeeXqQ3m6vLZQ0lfkufy91t/h9Tf4/f3Bvz86HtD37xJlD7ut0IM0cijY3xGkndtdQ3o9fvVNbtBah9/u9f2h5RSak3T6lJ1P8f7nDfH+pHqjCdJ+7fuJwvN8ievcq3zNbvtpu/vEJK3yYdJyD3jTqY2sNnY6FKqdO/+GqNvYBorPjUy6SlT/XAmDBZFfyjSzj78tBpDXXQJ899wC0W9Z9aLTvGnUH/4alZvW2zKdbnNzvnvuafrgK9b5+qAV05bx7CfoopUtXRRH4lyhpL8vzuXvvfnmVfF3cEJY8frjbv7+U/T9gqovzz2hLeeA3x8JnHFftUOrUg34d3i1s8xp2P4aMuWSX73kJL19+STtLF1tfzP6yTkCOYXhha+cpHc/5AHsIX10MyPeq+NNAvXTdv1E43P+rcVTdIku6M9cp8du8gBrE6yftrtPLNItn3TcA954tdHIma/R6bvv00eGIUEPtunm3ZM0q39D/G1sA8XXgZ0fsITu0DeX7tHRuTPNyd1nxQDiCBsJTl+2M8OrelvAI8efMbLLq86trm1zdO66zjbPmcytzPMBOXKBvqo/vzP6nCsRbLC/3/z+kcMZ9aKnqIz7PJjuLJ1S1l6X809B2jk0x8+IwbBVITC+3x+BnEJx/Dk6ff1jusWDjfh+92KFbvupu594n3+HPrgu7pPCy/ozQt4F0+Psvp+2vU/SLJ+03AMd8WsjTn5NdGnd/k33P36fqPn5XrSxDRRfB45+UfR8OdErOo/VEbnIbOUN+ixwfzInrIXVe1cfNmgNb0REwL8/tu/vkpEz72grU1ir1y+Iv7ebUId/O4dHhcKprVcR4vsj6Wd+PEMvCAt67c33KfuVdhZ82H7qcb5eMPHYYf3eTWT9NM3yUST/HuhAgDYaee5FOsrGl3zHio7sRTQ9bGMGis+NdL/1TXZohLLiEFuaqjNaxfbw3PD8CVGGviDruSOaE9YcPuHjPWIff38yeIbekiGXoJ2+h+186GWa5RDMoplrMeT390hObEHv3M3QC25PKXT7BTn/Hn2/k+ke5e9PsXycJPgeaEeQNtKykR45L7A69TWjrrdtDMXnQHQentxthj3YSmYrpMO8gUbNnTA8f3KP6JQzlGRbqmoCuytkh/AYNDwJ9vd7Eur7gyLa3RXW4TDITtOAEAT4O0K1cxc8O8/W+I2WlZH7//4I5BSE4xfFQNL5mayw7dfxfD1vbt8nQt7SmreI9venUz7pugdaCdZG0vi6uU1XPrxBpx2Rhx7dAxooPgFPKCv3+oKwQpap9i1rLoJvIo67N+h1y/2Wxbna6zR9rI+fUhP4zclgjmvzIgDrvI/phcsniTiEEXjS1n0NUbqI/Qf5+70J9/2BkWEd+zvUJLc5SHv9HVG0czeo77Px/v4gNkN4OYUhbPv5na9CkEebMtb1xqAe7e9PqXya7aNKEu8Bx3fLYodiA7URz8PSMl3as+dULXp5D2CvzjDwEmDhIWYvd7kgZcjAPoXxksr2vX2Jxs6Ra2AfXHAPJANLDvD4AACxc/+BudLyIV0p3WiZEgCgV0DxAQDi5+MFI3zlnhIAoLdA8YVBP/SJMCcA3tjL9HUx5tEB6DWB5vgWFhb00fRhxtXT/DvSDuQQL2jf5AMZJQOWg6/iGwS4s3HWXdBfIId4QfsmH8goGfgqvssr/0AfSTc8t8AhFvzb338BAKDfBFJ8XJdW2Lri3/DHf/Hv9BHQL848+2v06NEj/Q5EDd+/ab5XhwHIqP+wDAIrPrMuLZi/gT2Oq7d+X9eAfvDlv/Jf46aPEQyqyQcy6j8sg6FZ1Qml13/Y+OgvFZo9cIAONMsxWuWtVdvQWD3mWQ/agfYF6SAaxSeTGPZqe6X9wWE20F/6P8eXo7VHj2S49VF9hcb1URAVaN9IaazSMRgHsRBK8TWzIcusvcnG2+P7lH5PKMbfc4zLfOwy/TG//D/+OX3DXS+PedSb+NUPCcE9vgatHhMew7FV8ao/ZM5viwF8m873Iz1h7KB9k0xlVnvM2SJV9TEQLSEU30M6PKMfRpUbziabIB7fB3NakYFYCOzxNbZog+/46gZtwdqNHrRvgmlQdk57zeWCPgaiJoTiO0gjMeRJiotgc3x/QDfe/TP9GkRNUI+vsbVBtFKncqFKG8bILOeFhJeyKi1iDgFpz8UVDlLzR9pqPjBLFX3cH/ccVeu5zmu3/0zSQfsmmQxl4AXHztAsbvH3+H6V/t7Sr1L9t/8ZvL6YCObxVWipSDQ9maHcVIGqxSXnwFct0u7UIzVozywRrddpZdwewHngzBbHqKznmsqFEuUDh/SMOapO1vboPNWtzzwqU4FKtJiqSRi0LwBDo/gCeXxP/w16dQxeX1wE8vgqm1QqzKu5n9yUHPg2zZF5fIXmcupldWzKNUfEg3pVfGRODLEKHtypukt1/T4smVxO2OQWWXp8nC8f1dV7ANoXAHh8Tn6RfnX+LBG8vlgI4vFVNktUmGoOq8TjaskxMnvQ2KOa+KdazNqhsnxJHKnRXmROgxmuy5LQA6kC7QsAPL5Wful/oJO//gf0vywZg/Qv/RKlaDozsfh7fBUS4zKV8tbAd4DkuFraFDUByIzSmPhnfKWuQ2VWiWr1IA/Keao1r89hQF2VCtC+ADDw+Nrw5blv0Av/4g/oA/2e6Cl66teJPrj1qX5P9H9Xb1H915+mL+v3wB9fj0+G4crGgMpFzfMEc0qUB9MybxUxY6PWKF+n3TR5JGhfACQhFN8dWuBn+LicuyHe36NLJ/T7167RffWhxNDdzi1P0a/9T1/SrxVfnnuHXv3+N6QC5fI/f/As/a9zT+laxQdzqs4uzscj/OoHHW+Pr0Gri2YYzkKH4xZXA80j5dYeUX2lRnnt0agS1UPAOZoTLojtMW3SFC/SKOX7+kxcMNC+6cAI9Up3vErFrH4/NG0QP9irE/QM7NUZL9gHMvlARv0He3WCnuI/xwcAAPGDOT7QMwLv3AIAADECjw/0DHh8AIAkEHiOb2FhQdekC8zxJQfM8cUL5o+SD2TUf1gGgRTfueIb+mg64d+ADOz9h8PNvIQexAMG1eQDGfWfwIpvEGCPj+eY8G9//wUAgH4TONSZViYmJlL/GwYFWLvxgvZNPpBR/2EZDM1zfOhs/Qc3fbygfZMPZNR/WAZDs6qzrzRW6diBAzQb5z5PGpXPLKqdNBKGbzuaGxxzGdB2SCzB23+g+ylIPKEU3/2rZ+XcTbMs3tE1APQDI99bfYWwv3GvQftHQUsy3l5YzENGOI/v8NeovPOJXLRQe+8CHb1+gV65+lBXpgVtpTr2wdPHktThpLfjbyFnzm+LgSeq3fL7Q0VmADfLsGXhjhudWb2Pez8OQj+NDTMZLxsQpTwdg2scKaEU38jxZ2hEv6ZDx+ilI0Q7P3igD6QMM5km72KvX4JeogyOfG3FyMItSnlK14NIaGzRBmc9qG6QTqwOEoQjGW9mkqaRjDdyIp/jO/rFNGauK1ChYKdmaezVxPuCeqNpCT+YXogx92R6K14Oo/qc04Nzfod1fe19ZotUNXdqF8W2At1zKy4Pqfn3aUtff87597nqVoN5mFFSmc1TaVwove3zRhZugRgInDkFvH6HH85z23mT7eUwODS2NohW6lQuVGnD0HzydwsvcLXZN622CtJPg+DTTwWe99kQM/54Vr8CURCd4rt9lS7dPUmzZw7qA+liaqqgM1E3aGtjTLxXx5uY4Qedw2zRpRU4ncrmlPoMZ1PpBCu9xcc52aYd6uEbPlsco7L+jrJQxHkZitLzJnLOZFyMV9bf8Ii2m3EiY27F44tL+SztzqvP1WX6F3tQqcxmqUiWp1WnxzdY0fYSlSR1fHrSqfTa4PU7vOGB3PydZjvrT3SUw6BQoaUi0fRkhnKiz7fk1qsWaVf0YakUZ5aI1jkZrK0gw7VPgH4a4D4bKipLVKwWaB4x4UiJRvE9uEavnGvQxfcu0rP6UOrITVGBM1FzGGhsyuVhuMIPlKXH24QfODP1mj6R85ZZr22UBc3K0VZaDA9GVXH+XPN7eVByhF8jwPz7MqOcS9tCKZ3CvOVpZej8vIfmjhE7CWlnOv8OH2SITxgP67ZHmZsTBoUY7Jfk6N8bOfQVmYx2Xhlc3OfdSWiFxz2nf3xV3AfO8Tb+9glynw0NHKnJ14Sxu9YyHoFwhFd8rPROvE8vvfcOvZrGKGcTTsgprMuZDRprSdbJmGEa4TW0cYf8Bm32VNjbsAaWJo09qol/qsWsHeKRSShrtBehsdvx75PfP05JiKbUAvzgIMqxLfXdVi82M0pN1dkjOfSTirBw7GS0OgltsPTrPWof//tsKGCll92g6ToWAMVBSMX3kK68uUzZy2lXegoZ+qmOUave45sxTzXhaagQDId/dFUXFMqPqDxWpKw7NKQHX/Zk1PWt0stOX6X+GtZqEG4JvUVJ9vHWJfZ6MJckQg5xojx7O8P5AZJ6iyMd6gPexN4+0dxn6adBqzNFGitD6cVFKMV3/+oCXaIL9NXj+kDaya2Jm61zWMH2NOq0u09LNLcmbmbh9zmVX4BBXw46MSknvXLMtvzFACRHxN6SW1NzOvm4FjTI31ml4ozd9pWlIlWb4b0eKN9+IsOcZa1UrKLaPJjT15v2ieI+SzON1Zn2kSEQGSEU30P66OY9orvLlB/4h9hzNCcXUViW8iZN8eR8Kb+PZ6EydH5bDDZVofyM1XI8J1hfqYlB37bGnavp3H+DKJE9Zyj+pnX1vJDj9wlVu9+o4v5Qix/kgolmG3CJanUpt702PPS186UClY1VpP5ySCvCi1g0w5wWOty5uBponi7e9onyPksrvLhOaHs5PhhtHNm9Dhjs1QnaU5mlA3misocH3C3YpzBe0L7JBzLqPyyDaFZ1gtTTaJj2tPIOhHsQmdIDAICkAMUHFFszdliFV9Pxs26tz2MAAEDqgeIDErV3orHowb17CgAADAiB5/gWFhZ0TbrAHF9ywPxGvKB9kw9k1H9YBoEU37niG/poOoHiSwa46eMF7Zt8IKP+E1jxAQAAAINC4FBnWpmYmIDHlxBg7cYL2jf5QEb9h2WA5/hAz8BNHy9o3+QDGfUflgFWdfYCI19f3Kh8ZoOw0wjoOb791NxAmgv6GUgn4RTf7Uv2VmVcXrtG93UVAGDQMPLpyfyQIBZ41yTTwBia7dp6RzjFd/gMlXc+oZosy3T67jJ9/epDXZkWtBXr6Fz6WJL2x5PWuL+FrZ7HS+Ou7imRwwCgsv+bBVnOE0V2zpmMt1qkGbjWkRJO8R06SCP6pXhDjx3RL9OImUyTd7HXL0GPgRxiRBkS+ZqdgV6W8pSuB4kgkzE2j1DJeEG0RDfH92Cbbt59gl567qA+kCYKVCjYqVkaezXxnrMT2Ki5sw5WsjE3YlrTXo6K+pzTg3N+h3V97fVki1QV/xWzVv0BOtY8WX+mWVwWfPPvUxngrc85/z5X3WowDzNa/OXAtG8nC+/fEUyOXu2UXiqzeSqNC6Xn3pUnl3PtyRrm9zvPbZWPWwat9cCgsUUb1XGansQ+SlESWvHdv3pWze+lPAv71FRB56PjtCBj4r063mR03hl+EL7IoksrcDqVzSn1GZnVpwOs9BYf52SbdkiSB4NscYzK+jtkah4Z9tPzKnJOZZxW6tbf8Ii2m/FMY+7F44s5A/zuvPpcXaZ/sQedyqzen1Nep06Pb7Ci7T1+cujcTgrf3xFIjp3bKb2oJLTj05O+W9Ht//ez0jPbv1U+fvIDiqZxgCzssRBa8Y2ceUfN8b33It088TS9kro5Pk1uigqciZotrLHWrAQZYRXbfU+FH6qurLCcmdra15nzlrXu8aysYVaOttJiKrRUrIrz55rfy9ngHWG/CDD/vswo59K2UINiYd7yBDJ0ft5Dc8eJpxz82sn/d3QrR2c7pR87yWtn9v37tXeysm7kN5wTBlu1SEtSc/amnw8Czb1z69O0kTWjOyAKogt1HnqZZk8R7dzcTunKTk7IKaz/mQ0aa0nWyZjhRGHVtnGH/AYVtqTbZlZu7FFN/FMtZvX1RZEZ0Gu0F2F/7/j3ye8fp8ez+n1f8ZCDXzsF+h3h5ZhmagE61L5/f323NUqQGaWm6uxRPx8oMueJbbfqxha84giJTvEJDn/xCf0qnbD1Wa2OUave48EyTzVhCUsr7FGdVvYx4VwoP6LyWJGy7tCOHhzY0lbXt0ovQxxVcjk+faOjHAK1k9fviEaO6URlWq8Wl0QrxET28dZHHLSykySin6ePLFa3RE6Ei1uu0deX7tHRl44ZKz1TRm5N3ISdM47blnCddvc5AZZbE4Ot8Pucyi/AoCQHjZiUU2aSpsW9pebWGKEgpCXeJzrKwaedAv6OKOSYRnJrak4zH9eCEtn+VSrOGHOuS0WqjltRjh4o30GjsUozHB4OMDcLghNO8ZkPsJ9Ypp1Ty/TtM2lc1elHjubkJL8VItukKV5EUsrv4+HSDJ3fVs/mZI3VhjwnWF+piUHJ+g4u5qpK998gSmTLDcXftL5C4/x75LX17xOqNmlRP+928vsdUcoxjahFUHJBSdv2Cwv3bW3Y6WvnSwUqG6tI/fs5IPMBdl7NXSi71gSAsGCvTtAevvnyRGUPD7hb+rJPYQy/I6lgH8jkAxn1H5ZBpHN8IL00GqbJ3aDVxRJRoXV1a9IZlN8BAIgPKD6g2JoxQk/6WazW5zGSz6D8DgBAbEDxAUnzuSGruHf3SAmD8jsAAPEReI5vYWFB16QLzPElB8xvxAvaN/lARv2HZRBI8Z0rvqGPphMovmSAmz5e0L7JBzLqP4EVHwAAADAoBA51ppWJiQl4fAkB1m68oH2TD2TUf1gGeI4P9Azc9PGC9k0+kFH/YRlgVWeXqHQh2GkCaOQuG8gppzA3AOfS+T7BfQT6SWSK79Yib112iW7p9wCAYcPICynzR4L90MzFZ5VByYScIKJRfLcv0evX9es0ojNvOzqbLK0WqXpODLvJx4q5V6EuuPejRGdJ7+P+pLiPPDCTJbMBUcojH1/EhFd8D67RK+eI3r58Uh9IL5w2yPHwM27MniOt3XzJJYsyuRPigxDIhLHi3+oGbWE8TRyOZMk644g7WTIIR0jF95CuvLlM2csX6Vl9ZDBxz1245nS0xzhb0Za0/pzppbSEL8xrBDifab2G0yN11qdw3slKwbJiZwBX5EiMBU06/s5mO7LTaNXvRw6dzxcfMmQk2n9PH04Rja0NItHG5UKVNgzNJ9tGeIGr8rdz37J+a1T9zOc+EnjKZ4gZT0aW6IEhlOK7f3WBLo0u01vH9YGBxZi7kGlu2sMZ1nfn1efqMv2NcdOa4QvhwXBetEVX+MLrfB4QskUer6xrcLE9UlU/RmVdJ1PPpC3VjszgXaB5Dzc7yO/ktEObU1a9PmgRSA6dz6/M6v0/5fnrRBt9zFu4Lyq0JPrR9GRGJfx158arFmlX/HapFGeWiNY5Wa+tIMP1swD3UQD5DBWVJSpWve8J0D37V3wy8WyG3p5/Rh9IP45cd6K4PS4/TE8lM8q5pm0c4QvKEidVdocvOp/PgxV7QusdQq9W/VwzCwEPauILKE0BksZeM1d3B4L9TrMdOf+b6T12Kwfn+RXa5GQP89b+nxk6P99hAE8qlU0qFeZVP8pNScXSzNvLNJPGinYZm3L1t/j7WRD5DA0cgcjXhLE7+Cm1es2+Fd/9j9+nHbpBr1uJaM/dEEfV+4Xb6jNpwz3HZw6YQbAze7fDDPMIr6FN5u+O5zf2iFWCX321mLUVt8w8XqO9FBvLLeHGgL8zXjmMU5qjThWhuQtTTbUlM6LbGet96Ek/85fPUMBKL7tB03WsM4iDfSu+kTPvUG3nE7vIxS0n6W3xevBDn93CN3OeasKTUEqVw0e6KgiZUWL/r9ZpdNH17KmYijtti3Myk9NCrdgeCHtbKtylCf07Q8pBUqX0OiDKYzUjG1JvlTZFTQBi72dRyGcQaNDqTJHGylB6cRFycQvoBtuTqNNuV5ZsjubECMCWdvvwq7LcW+Zr0kbmPK3Luc3Wx0gU0fzOfctBr7BrekgyFMWaIyXIMGfZUFhc1DxaMKevN/1s//fJYNBYnZHzyFbIGUQPFF9PUIrLtrQ3aYon90v5wM9S8XNPasGLba1zsRQhe0f1lRrljbp2zyEmHfl8V3mMilnrN+TFsGwT7neGlUOGzq+r56rk+TNE650WaSQO4UUsmmFOCx3uXFwNNE8Xbz8Lf5+knwZt8bMm1SJlzTbudsEB8AR7dYKegX0K4wXtm3wgo/7DMoDHBwAAYKiA4gMAADBUQPEBAAAYKgLP8S0sLOiadIE5vuSA+Y14QfsmH8io/7AMAim+c8U39NF0AsWXDHDTxwvaN/lARv0nsOIDAAAABoXAoc60MjExAY8vIcDajRe0b/KBjPoPywDP8YGegZs+XtC+yQcy6j8sA6zq7BKVLyx9O6IMBEa+vLBAjilBZuNHTj4QLeEUH2dft7Iz6PLK1Ye6EgAAQNdIZW9sVzY027X1jvAe35ELVDayNHz7zEFdkSK0J+HobLK0egRyL8mUZT1IG2Y6IlWit/iHWo7ugVUUbAWZILJzzmS81SLNIDQRKQh1Grjz8UHB9RqViy1fszKc61Ke0vUgLDLEmy+5+nqZ0MIJIpNpScYLogWKLxBmckwuLg+kOffUoNVj7a1oNafU4RoBzmdar+H0SJ316ZsXqczmqTQulN62leFck8uRM6dAh3Yy5gBbkthKfOTItHhDAzQPKNpnRmZQtzPMK0T7Gu879iPf9nWf2/354kOGbEXb7+nDw0pjizaq4zQ9CQs8SsIrvrvLlJfze2fpygN9bODI0VrT++ichqaUz9LuvPqcSiFk3PSj887wBZVo0TWiep3PA0q2SLRSt67BxfZIVf0YlXVduVCifKrmBlSS1PHpSafSa4NnOws4rc3mlNUO+qDET45CMbZ4QwPk9dd3qSp63rzHDwrSjzq3ryBQP+98fmU2K3PRqWusE22kKN9hhDQNCGRhj4Vwiu/Qy/RtPbdXniO6dOJpWrit61KInQdMFacl6o9pSWdGOVe1TUaY1HbfVeGLqiuVd+fzK7QkLfX1DjeAVT/X9IxyMmPobqAca0nCTkLaGa92Zsx6zh/n9G68UHLh3HSD4uSZNPZq+lUngvUjr/bttp87z1fGT2He8vgzdH6+nYEy+Kg5aKH869O0kT1AxzDHFymRhTpHzrxFF48QvfvhHX0kfbjn+IIPmArvQdsMswmrtk1m6Y7nN/aIhyy/es7Q3lTcMjN4jfZSdr/UAvzBfsoxiPJsjxhot4XshaMxY7Vjt9ZPimgJNwbsR/H283F6PKvfA9aAxLq/urE1kMZYv8AcX0/gwSBPNWHpKqVap5VuJqwzo8R+TUeloOvZkjYVd7rCdCoTeLW4JFqrz4jBZpvbr66yrQ+KtZ2ZnBZqpUSbuoHZ21LhSE3ofhSyn0uq5HIQh54sVrdETnSK7/ZVunSX6PRXntEHgBvb0q3TbhtLuDM5mhMjCFvi7R2QBCmNEOTW1JxQvl8LcyqrzoUsWhHs34NMGEKhr8s50U4LdqLpR/vu55lJmuZQs6WZeTGM9DiHGGtBUoC5bxCccIrv9iX74fVzN+j05U/oreO6DhgoxWXPIW7SFM/qC28i6MOpHPNXCzmsa6hiKUK23usrNaE0zPq0rUhUi0/kgoq+/I5dKmbN71XeS7ch7yQj547KY8bvzAtTwyZcPwrbzzN0fl152fL8GaL1touQBhxzZXG2SNVCmbaxuiVSsFcn6BnYpzBe0L7JBzLqPywDzPEBAAAYKqD4AAAADBVQfAAAAIaKwHN8CwsLuiZdYI4vOWB+I17QvskHMuo/LINAiu9c8Q19NJ1A8SUD3PTxgvZNPpBR/wms+AAAAIBBIXCoM61MTEzA40sIsHbjBe2bfCCj/sMywHN8oGfgpo8XtG/ygYz6D8sAqzq7RKULSduOKKAjcpeMPm2RljSMfHlhwX0Ckkx4xffgGr1ibVvGZTG92RkAACBJqAwaMMyiJpziY6V3Yplo7rrMySfLfAo3qdaWbnN/vGZptVhVniwkhowPM61NZzmA/WOmI1Il+oEV90kEVGZp2PfojotQiu/W+jLtHLlAf//MQX0k3bjz8eHG7TUNWj1mprWxyjpN6k+AMCijIl+zMpzrUp7S9SAxyMwUROVh3KS7B4RQfHfog+tER186RiP6yODi9kJcFnJzboQHbvtz5lyJmvPocI0A5zOt13B6Qs76NIZHVBqb1jRAGcroQ+Hb0awT7benD1vH20xwye8MmEUjyVRm81QaF0pv28pwrsnlyJmAokP7NdvX6TXaTeZznzBm5gFZ4M23Itp/pkhj5TWXXEBUhJ7jyx5+QAvNOb6zdOWBrhgoVLocZR13tsBK+SztzqvPqRRCxo0/Om9Y2Srv3KLrjvc6nwffbJFopW5dg4vtkar6MSrrOpnaJ3WDdZY45yantemY/DVkO1Zms1Qky+NZJ9qwYkkZOs+prkubrsG6QkvFKhXmXcoidVRoU/zUIHndPPuxgOWzOaXqnbeD330iFGO+5IqsIKriprE6Q8Wx8kClw0oa+1d8D+4L+5zo3XMf0wt6fq88R3TpxCW6pT6ROjrlugsKZ662OmtmlFOY2mSEVW3f32qAr7pSTXc+Xw2+4yvrHQYJq36uaSHmZEbRXSmj9CCUz7YabDnprpSDSwhh25EHf1uJaWVnkZujlXGnIm2sLkovaW5ABqEgSXW9+jFj1nP+Puu1P9qwWUy/9xwbwqueYQM2eKOCfbB/xXdoRHRjoqNzZ+hZdYRGnnuRjoou/VlKvT73HF+3fc97UDHDQMLraJOZuuP5jT2qiX/86pvKgoucFa/RXgpHGLUwgmUgPDqZlNT0OMK24zg9zh23LUoR2hnIG7S1MQjenk0tQIfwU45+9Z1Rhg072jOWDLu1LgecxtYGVclIxCzvY/UeTRUdIUKdh+ixI/qlhVaGwA0P1uaijbrwLHRVEDKjxHZ3x0FL17Mlbiru9IeRcrRWXxGqylLgIdtRUiWXg+jE9PoqSzIsOhjeXo5kEKCp1PtI5jxts/xYtsKw6RjWHkJso08XGTIuyCkMOIHREULxHaRXCydpZ2mhOa93/+o/onePvEjPH1LvgRPbUlaLOIKTozkd/mtv9SVoUAuFUGyuH6gs4DEynYx9t2NmkqY51Lapv4MXa7SsF7e8vhk6tlgaKG8vt6bmRPP9WvhUWXUuZNEG2/49SAD2R7jFLccv6nk9tbglv5Sht7/18hCs8uwWpbjsOcRNmmJLjsN4ARegsCWoFhpY11DF0hM811JfqdkhEllSuGJOhjbt36AW7Fir28K2o1Bq68rLkOfPEK23W4SRmxIKoir+G5y5PYVafCIXPhlt3Lt+skvFrPm9ynuHJwN6DfbqBD0jPfsUqpCq0LipGpSxD2TygYz6D8sgnMcHwAAyaCs5AQBOoPgA0FgPZcvwqvshbwDAwADFB4CG50nVajrsmAHAIBN4jm9hYUHXpAvM8SUHzG/EC9o3+UBG/YdlEEjxnSu+oY+mEyi+ZICbPl7QvskHMuo/gRUfAAAAMCgEDnWmlYmJCXh8CQHWbrygfZMPZNR/WAZ4jg/0DNz08YL2TT6QUf9hGWBVZ5eofHDIIQZAT5D5+/q0xRoYWEIovjtGHj6zDGpOPgAA6AHuZL0DkAQ5aYRQfM/QWzoPn1XKc08QnfoavZq2Tap1ZmlHZ5Ol1bNTu6cjeWZstGTohrUfOS1tjJQ3iSI750y2XC3SDEJMkRJhqPMOfZM3qZ5/Rr9PH+58fFBwfSC3Zrd/2yzeIAwyVN+SBb1MU7oeJIBMxtg1SCXvBdESmeLjlER0+WIzKe1gYSY/5eLyQrTHOFtp0Oqx9la0mhvscI0A5zOt13B6pM76wfWUOv5Ov3b0rNfH2rg+8vsGIdwkfv+MzNTvzoiQo5zx3r992Wl0t5+itY92d774kCEb0b/39OFhpbFFG9Vxmp6EBR4l0Si+B9fo6zdfpK8e1+8HDpXOxc8LKeWztDuvPqdSCBk3/ei8M3xBOtmpgdf5PKBki0QrdesaXGyPVNVzCh9VJ1PPDODcQJDf6SkHQft6lYePSpuOz7LRsySUxUDk5avvkvglNO8RxgjWvgdoc8qq1wctAvXzzudXZrMy+a+6xjrRhjtf4nDQNCCyGzRdR+QpaiJRfLfWlylbSH8evk657oJiWtKZUU6xaZMRJrXdd1X4oupKBd75fDX4jq+sd7gBrPq55h6TOZmZdpe8ko2nj2C/00sOTMd6M/u6ZpAyNTT2avpVJ7pvX97f1HrNdNvPnedXaFPoOdvI0MbIENLMxF6fpo3sAWSpj5jwik94e2t7FwbC23PP8Zk3dBC8M0mb4VJh1bbJHN7x/MYe8ZDlV88Z2puKW2YWr9HeIN0vAX+nX0bvzvVW9nUrk32DtjYGxNtrQ0u4MZL2DdvPx+nxrH4PWAMS6/7qxtbARW/6SWjFNyjeXryoxKacbVop1brwLHRVEDKjxH5JrZMW0/VsSZuKe+AW5/Tid5peX2VJht0GJS9fZnJaqJUSbepIhspGweFITej2DdnPJVVyOYhDTxarWyInnOJjb+/6SXphYOf2osW2dOu028YS7kyO5sQIwpZ4+/BrjmREqumpDAYVGfeaaobdevM7La9vho4tlgbL2xPew7qc0+y0AUM07bvvfp6ZpGkxxpcszcyLYaTHOcRYC5KmJwcy6tAvwim+zz+jnVPPDehKzihRisueQ9ykKZ7VL+UDrxbkmL9aiGFdQxVLEbL1Xl+pUd6oS98OM87Vs/naCtVd8eae/M7clPCCquK/wcvCLueOymNUzFptlxc+oE249g3bz4XRsb5C4/x5Pn+GaN1jMdnAYj5nmS1StVCmbaxuiRTs1Ql6Rnr2KVQhOyp3P8/bT7APZPKBjPoPyyCSVZ0ADBKDtJITANAKFB8AGmuVo3yObXswV3ICAKD4AGiiVjlyWTMW1AAABo3Ac3wLCwu6Jl1gji85YH4jXtC+yQcy6j8sg0CK71zxDX00nUDxJQPc9PGC9k0+kFH/Caz4AAAAgEEhcKgzrUxMTMDjSwiwduMF7Zt8IKP+wzLAc3ygZ+Cmjxe0b/KBjPoPywCrOiPBueNI+nZMASAKgt8HKu0O7hPQH0Iqvod05bWnaeyoVS7RLV0zXBj5+uorhC1lwXCC+yAKmrn4rNJtfjTgSyjFd//qAl0aXabazieyvH3qBr2+eEfXpg23tcoFFmnvgRziR2c572OiYpVvDglW22Im82UDopRHPr6ICaX4Pv/BPTr6xUP6HdHhLz6hX6UNHgjMdCpWWadJ/QnQCyCHntDYog3OmlDdoC2Mp4nDkcxXZ6xwJ/MF4Qil+FjR7SydooXb/O4OfXOJ6OLMM7IuXaj0Ka0JMjOU0Ydawg8HZoVv0h3Oa7jON3dkl2UYvRwfOXCaGtE2ZuRHbjPmPODZjp5ybF5fe0T6M4MWaWpsbRAJ46JcqNKGoflk2wgvcFVu3cbtZrWDVxt2cx+4vfnWc6O4zwaRcWTnjZRQim/kzDtUnnuC3j3H83sf0ws779CrtgOYIrLEuR45nUrHkIIZfpDJO3Wy0oDwDS33gNTXKBdKlG+GmsSAkC+5MsAPYxgogBw8CdCOAeRYymdpd159RqWCGqTBt0JLRaLpyQzlpsxs85pqkXanuH8KpTgjLNl1TiZrK0jvfuyHMQfYKd1QyPts4OBkyNUCzSMmHCkh5/jOUn4pQ2/zHN9lotePPq29v7SRofPbapDjZK/S0nSZ+Y7wgx6gg4cfeLCp0vjKXHMPSB50xAWEj8PoAX+xf3MuycBfDt74t2MQOXIGcisdUWaUc5IPEJVNKhXmlTEg8w7aGdklRlaK6tiUy/jy68fhCXefDRgyEW9NOOfYOzZqQig+Dm3eo9OXL6pEtMcvau8vvSs71YS7tjRlMkzT0jfDNFlhhenDQWjsUU380xzMucjM0jXakyO0GvAfrRPNWPWDFl/rAm85eBGkHf3l2BpqHRw4q31hqqm2ZMb1ZsZzP3z7cRSEuM8GCVZ62Q2armMBUByE8vjcjDz3Ih0VtvZnD/SB1JKjNbkc27qh+WY0F11w+Ed+MBiZUWK/gT0Jdb5VXJ06c562+ThWcmnccnDToD0eid10bMeQckw9FRJ6T4aSLcUl9VZpU9QEIGg/3jfDLh+LBq3OFGmsDKUXFyEU3yF67AjRu6VrdF8fubW+TDtHXqTnUzfPJ244l2fACwCq4jY3jX/bE1CLMIKjLOuW+RSLyqpzIYseYAbZ82iPjxx0u9S0FqzMujyCgO24fzmmHBnmLBsKi4uaRwvm9Pn044gYWvloGqszVCQkQo6TEIrvIL36rWU6fXeZ8voB9tf3LlD5Wy/TiP5EqpAhNdsSVhP4Vmw9R3PC9LQt5U2a4sl5PifgxD7nequv1ChvfIe9Wm6XilnzuLJ6rXmmoSKAHKxQ26ZchCErNH7tGF6O6UV4EYtmmNNChzsXVwPN03n347AMs3wsGrTFz5pUi5Q123iIpz7iAHt1gp6BfQrjBe2bfCCj/sMyiHSODwAAAEg6UHwAAACGCig+AAAAQ0XgOb6FhQVdky4wx5ccML8RL2jf5AMZ9R+WQSDFd674hj6aTqD4kgFu+nhB+yYfyKj/BFZ8AAAAwKAQONSZViYmJuDxJQRYu/GC9k0+kFH/YRngOT7QM3DTxwvaN/lARv2HZYBVnZHAewwauyxEtpMFCI3Mz4ecbgAAm9CK79ai2q5MlsU7+uiwYeQZk5sqAwDAPnEnUx6a7dp6RyjFx0rvdVqmGufj27lOF/cupFj5ub02LvDc+oPO/I0bPgbQzxNPds6ZjLdapBkIKFJCKL479MF1otNfeUa/P0ivFk4SXf84hfn4eKA106FYZZ0m9SdAD2lsEe/TS9UN0om/QSSgn6eCTKYlGS+Ilhjm+NKYj0+lP2lNAyQ6oD7UWD3mspK7nzdyXsN1vju8McRWOKciIjE4lwtV2jA1HyfnFG0zW9EeoW4r58b1Zp1owz192DreZpd7KZeh8C59+nmzfdVRpjLrajOffup5nwSSH3AgjcBxmp50ywyEIYTic+fje0hXSjfEv/fo+5/LAylCWVWcDqVj8tfReWf4gUq02IVm4gFBpdhR1ygXSpRvDrYVms2XqFC2rs9lWJNQVmipSPJGz00V2uZ+K+WztDuv2qku09jYg6vM0UcrWlbrRBucaZXJ0Pl5TnHjTrrK31elwvx5w8oeVAL0c08C9NMA94mX/ICiaUAgC3sshFB87nx8C0SFC3SUnqDHDuuPpAYxKG6rm9DK9eb2DDK5nDEwqgGkuhskgxmjBtfxlTmysqHxoC4uoHOg6QFpEXNaKlnqvLrRc1Ntk6RyBnArx15mlFPNWqgM47YS08rOIjdHK+POgbixukil8WFJ+unfz73x76dB7pPO8gMWmfPbynioT9NGdr+GCuhEyFDnM/SWXNjC5R16lT6jHV2TRpqdjS1VmRDVtETNRQGuzN9+NPaoJv5pDjZchOVM4qhKJq4GJHZQZqz6IY3/VITmspOl6iSpLs3XGqrTyHYep8ez+n0LShHaXqRK+jkc3p6Ndz/3Ikg/9b9POsoPtJI5T2y7VTe2YBRHSKRzfLc+vEF05EV6/pA+kFpytCYfS7AUE9/M5qKAuvAc5AeDkRkltmvZ0lXnW8UVwhCdfJuP83eLAWn4rDzlsdkZuA+QtA9awpNeVMnTETe9vsqSDIsOh7fXDnc/d9OgPbbY3HTspyHvE9CWLLvNIFKiU3y3L9HrvMqz8DKN6EPpQdywLsuVF1hUhboyjVPbUlWLBIKjPJd281WSyqpzIYtWlENnGcswZ9kwDLioeSJ3uLMtmUma5lCc9WFeTCE1p4nl9c3QsUXhXQ6Vt+fTz3W/q2ktKOdLzX4esJ/u/z4BLYg+PMPTJNOTQxWViJtQiu/+1bP2w+vnbtDpy5/QW8d1ZdqQIR/b01ALUdb0nFyO5uQkvFW/SVNljsGJcwKuBsyt8dxKjfLGd9gr4napmDWPK6vZmgcZDhq0yoqoGea00OHOQPOfQqmtKy9EtuMM0TrLyY2cO6yK/4bQ2wvQz62Q/OYUL8KSFRq/fhr+PgECc+VstkhVYQxuY3VLpGCvTtAzkrNPoQrJiRF/oIwL7AOZfCCj/sMyiHSOD4A0MFwrOQEAbqD4wNAgH8Y+oMN728O1khMAYAPFB4YGnmdVC2asOS0AwDASeI5vYWFB16QLzPElB8xvxAvaN/lARv2HZRBI8Z0rvqGPphMovmSAmz5e0L7JBzLqP4EVHwAAADAoBA51ppWJiQl4fAkB1m68oH2TD2TUf1gGeI4P9Azc9PGC9k0+kFH/YRlgVWck8APR1m4VXJDROu2otDCQIwCDSDSK78E1euXoWbrSJgHtrUW9pRmXxTv66KCRozVrb0m56S8AAOwTd7JfbPcWOaEUX1OpnVhum46I9/J8fe8ClWXaout0Ubx+5epDXZs03F4bF1j8vUO3v+Mm18f6kKJJpe4ZxASg6OeJJzvnTOZbLdIMBBQpIRTfQzo8o3PxXT6pj5ncoW8u3TOyNRykVwsnaWfpKt2S75NEg1aPmelUrLJOk/oToEc0k/MKOFuDfgmiAP08FWQyxq5CKpkviJYQiu8gjXjl3XtwXwxgrmzsh79AR8XN91mbkGh/UelTWtMAiQ6oD6k5H9NKDpq808Z5Ddf57vDGUFrhBSoU7BREjb2aeO/MruAvBx7c7frZ1VU65mhLr3q3N9Rexp5yTDQ+/ZzTOInfZDrYcps35wHjt3Nx9lNP+TSv75JBehqw9zS2aKM6TtOTbpmBMMS3uOXzTtnY79H3P9cvE4OyqjidSsfkr6PzzvCD8EVkMtOA8ICgUsCoa5TFAJ9vhvXEgJsvUaFsXZ/LIIbZ/JmaEi0rNR9nRx8T79XxJj5ykDnkaEV/pk6PbxTJkVLOs96Yq22XzkjgLcekE6CfexKgnwa4T0r5LO3Oq8/UZRqjNBkPvaFpQGQ3aLo+nGNBnMSn+FJFhs5vq5vQykXmnlfK5HIt4YeqZ6pvkwotcTLJlbnmHpE5mZnWCuvpASlQzrkBh3PlccZ1tnTHplr21PSWg8rgbieXVUlnbfzq/fCTY9Lx7+fe+PfTIPfJuJHDLzPKqWyBGzXHLIyD+jRtZPdrqIBOxKf4ZFizHa7wZ4Jodja2VGXCTtMSNcNgrszUfjT2qCb+aQ42XGRm8BqpZNdqQHq0TjRj1Q9t/IcTzwovYWaDxlqS0jIecpDtPE6PZ/V7N371fvjKMR1493MvgvRT//ukNdQKOpI5T2ybVTe2YBRHSHyK79CIsPdcYU0Z/szQF7zmBhNBjtbkYwnWgMY3s7kooE7CaA5OZpTYrmVLV51vFVcIQ3TybT7O3y0GpGG18tiLqlbHqFXvBZFDlbwdcb96D4LKMTW4+7mbBu2xpnfTsZ+GvE9AW7LsNoNIiTHU+Qx9de4Jerd0je7L9w/pSukGHZ07Q8/K90lC3LAuy7WxtSGGyDEyjVPbUlWLBILDXgx7Ckvim9pQWXUuZNED7NBaxrk1MWh2Th3UUQ6ZSZoWY4SaI2SEXKVHpvGr98VHjonHp5/rflfTWlDOh5rtG7Cf7v8+AS00VmmGw+vTk0YIGYQlhOK7QwvWg+nnboj39+jSCf3+NaXsRs68Q2+PLlNefu4UXRKvv33moDw7cciQjxWi0clKm4NvjuaE6cqLAlT9Jk3x4gc+J+DCBs4FV1+pUd74DntF3C4Vs+ZxZTVb8yDAwk8OGTq/rrwQR33TgPGr98dbjikgQD+3QrmbU7x4R1Zo/Ppp+PsECMyVs9kiVQtl2sbqlkjBXp2gZ/Rln0IeRPJkDO4u/OpTBPaBTD6QUf9hGWBVJxgoGg3Tr2jQ6iIv47RXh/rVAwAGHyg+MFhszdhhIl5VyM/smTFjv3oAwMADxQcGCnupvi7b1jN7Cr96AMDgE3iOb2FhQdekC8zxJQfMb8QL2jf5QEb9h2UQSPGdK76hj6YTKL5kgJs+XtC+yQcy6j+BFR8AAAAwKAQOdaaViYkJeHwJAdZuvKB9kw9k1H9YBniOD/QM3PTxgvZNPpBR/2EZYFVnJPAehdYSeS4p2sljmGiTbw4AMHxEo/geXKNXjp6lK50SzPrVpx4jj5vc9BcAAPaJO9kvtnuLnFCK79ai3pvzxHLbpLN+9cnC7bVxgefWW5QMnB4ZH+siUan06iC3zqCfJ57snDOZb7VIMxBQpIRQfA/p8MwnVNsR5fJJfczErz5JNGj1mJlOxSrrNKk/AXoHMnLHBfp5KshkjE0VVDJfEC0hFN9BGvHMq+dXnyRU+pTWNECiA+pDjdVjLiu5+8HZeQ3X+e7wxlBb4SVa7PDjO8tBezK8m734z8wi0JrXkBWAXW96mJ5ybs4Rdj4/2fj08zZzoJVZ8RudB4y24eLsp4Pdfn2gsUUb1XGannTLDIQBi1skyqridCodk7+OzjvDDx6Dczt4QFApYNQ1yoUS5ZuxezFo50tUKFvX55LW5KZhKVC5XOic866jHPQ8q5xjHaeVuvWZRy0pXUr5LO3Oq7q6TKNjDM4B5Ox5fqIJ0M89CdBPB7r9ekfTgMhu0HR9WMeC+IDik2To/La6Ca1cZA4rV5DJ5VrCD9XAqbwrtMTJJFfmmlkAOMu4uICwwRk9IC1iEluSm6OV8faGRTg5KDiDurUvdWaUU6naBLm+1/nJxr+fe+PfTwe7/XpHc0/Z+jRtZPdrqIBOQPEZ2BsYC0tVJuw0LVFzUYArM7UfjT2qiX+agw0Xmfm7RirZtRqQHq0TzVj1Qx3/UQljqa3XF0IOmtZQn4n/9b3PTz7e/dyLIP108Nuvp2TO0zzbyBtbMIojBIqvLTlakyEzSzHxzWwuCqgLj0R+MBiZUWK7li1ddb5VXCEM0cm3+Th/txiQhtrKkzd8ifKzm/oAE1IOvsR9/aTh7uduGrTHFpubjv102NqvN2TZbQaRAsUnETesy3JtbG1QVagr0zi1LVW1SCA4OZKRzU7zVpVV50IWrSiH3TLOrbFHUiL2jU085SDbrkpdRj8d7F/OScenn+t+V9NasDLr8tgC9tPBbb8+0FilGSGE8elJI4QMwhJC8d2hBX5Gj8u5G+L9Pbp0Qr9/7Rrd961PGDLkY4VoDuiFKGt6Ti5Hc8J05UUBqn6TpspCk/E5AR8uza3x3EqN8sZ32Cvidh2rEA9oqxn5UVW72wSRg/szogQOG4eXc+IJ0M+tkPzmFC/CkhUav346BO3XC8yVs7xKuVBuWaAFwoG9OkHPwD6F8YL2TT6QUf9hGSDUCQAAYKiA4gMAADBUQPEBAAAYKgLP8S0sLOiadIE5vuSA+Y14QfsmH8io/7AMAim+c8U39NF0AsWXDHDTxwvaN/lARv0nsOIDAAAABoXAoc60MjExAY8vIcDajRe0b/KBjPoPywDP8YGegZs+XtC+yQcy6j8sA6zqjATeo9DarYILMloDAEBSiUbxPbhGrxw9S1ce6Pea+1fPqi3KrLJ4R9cMGjoXnLVxrz4KAABd4072i+3eIieU4ru1qBXaiWXa0cccHP4alXc+oRqX9y7Q0esX6JWrD3Vl0nB7bVzgufUeHznILN6Qy/5BP0882TlnMt9qkWYgoEgJofge0uEZrdQun9THnIwcf4ZG9Gs6dIxeOkK08wOXW5gIGrR6zEynYpV1mtSfAL0AcogXtG8qyGRakvmCaAmh+A7SyCH9sguOfnEfJ8WOSp/SmgZIdEB9qLF6zGUlB03eaeO8hut8d3hjKK1wLzloT4V3qxf/mVkCzLyFnnKS3uIBmq2wArA/Mzw5f336ebN91FGmMivayHnAaFsuzn6K9o+YxhZtVMdpetItMxCG3i1uuX2VLt09SbNnDuoDSUJZVZxOpWPy19F5Z/iBSrTYhWbiAUGlgFHXKHOS1WbsXgzq+RIVytb1ubiS1A4FXnLQ86hyDnWcVup2WzlStgSQUymfpd159Zm6TKPTvRGTTgL0c08C9FO0fyQ0DYjsBk3Xh3EsiJfeKD5e/HKuQRffu0jP6kPJIkPnt9VNaOUic+dwy+RyLeGHauBspxVa4mSSK3M675kYxmVm2l1hgzN6QFoc9klsfzn4EUROnAnfyiGXGeVUqsNC2Pb176do/2jInN9WxkN9mjay+zVUQCfiV3ys9E68Ty+99w69msQop0Gzs7GlKhN2mpaouSjAlZnaj8Ye1cQ/zcGGi7CcSRxVya7VgPRonWjGqh/i+I+3HPzwl1NrqG+42H/7BumnaP9IyZynebaRN7aG3CiOlpgV30O68uYyZS8nX+k5ydGaDKlZiolvZnNRQJ2E0RyczCixXcuWrjrfKq4Qhujk23ycv1sMSLDy3HLwI6Schg6/9m3QHltsbjr2U7R/HGTZbQaREqviu391gS7RBfrqcX0gsYgb1mW5NrY2qCrUlWmc2paqWiQQnBzJyGZxSXxTGyqrzoUsWlEOn2UcQA6ybarkFWXev5wGHZ/21f2uprVgZdblsQXsp2j/CGms0gxPk0xPGiFkEJYQiu8OLVgPpp+7Id7fo0sn9PvXrtF94e19dPMe0d1lyluf45LUh9hlyMcK0RzQC1HW9JxcjuaE6cqLAlT9Jk2VhSbjcwI+XJpb47mVGuWN77BXxO06Vike0FazNQ8yVHjKgXHLQpTmYB5eTgNPgH5uheQ3p3gRlqzQ+PVTtH8kmCtneRVzoexcwAVCg706Qc/APoXxgvZNPpBR/2EZ9GZVJwAAAJAQoPgAAAAMFVB8AAAAhorAc3wLCwu6Jl1gji85YH4jXtC+yQcy6j8sg0CK71zxDX00nUDxJQPc9PGC9k0+kFH/Caz4AAAAgEEhcKgzrUxMTMDjSwiwduMF7Zt8IKP+wzLAc3ygZ+Cmjxe0b/KBjPoPywCrOiOB9yi0dqvggozWAACQVKJRfJyB4ehZuuJOrn77kr1VGRe5ldkgonPFWRv36qMAANA17mS/2O4tckIpvluLWqGdWKYdfczB4TNU3vmEarIs0+m7y/T1qw91ZdJwe21c4Ln1Hh85yCzekMv+QT9PPNk5ZzLfapFmIKBICaH4HtLhGa3ULp/Ux1wcOkgj+qV4Q48d0S8TR4NWj5npVKyyTpP6E6AXQA7xgvZNBZlMSzJfEC0hFJ9Qat3k2HuwTTfvPkEvPXdQH0gSKn1Kaxog0QH1ocbqMZeV3E1yVIXzGq7z3eGNobTCveSgPRXerV78Z2YJcOct7NTO8nhL2IiVgfMannJKNT79XHrTB8jMXFSZFW3gPGC0DRdnP/W8T5rXV21ufcaVKQmYNLZoozpO05NumYEwxL645f7VszocmuQs7Mqq4nQqHZO/js47ww9UosUuNBMPCCoFjLpGuVCifHMQFoN6vkSFsnV9Lq4ktUOBlxz0PKqcQx2nlbrdVmbKFq92zpyfp0J1g7bMS8uBpUDz+hrecko7Afq5JwH6aYD7pJTP0u68+kxdpjEaJOMiGpoGRHaDpuvDOBbES+yKb+TMOyoc+t6LdPPE0/RKIuf4MnR+W92EVi4yh5UryORyLeGHqlc2VAcVWuJkkitzOu+ZGMZlZtpdYYMzekBaHPZJbH85eOPXzpwvjqi4ZF+TE7FS8/N+56edsO3r30+D3CfjRg6/zCinsgVuMue3lfFQn6aN7H4NFdCJ2BVfk0Mv0+wpop2b24ld2dnsbGypyoSdpiVqLgpwZab2o7FHNfFPc7DhIixnEkdVsms1ID1aJ5qx6oc4/uMtBw9821lce3Kaxkub+nqs6MgOIwU4fxDYd/sG6qf+90lrqBV0JHOe5tn22tgacqM4Wnqn+ASHv/iEfpV0crQmQ2rWgMc3s7kooE7CaA5OZpTYrmVLV51vFVcIQ3TybT7O3y0GJFh5bjn4EKSd5UCiw2+VTSoV5o26gHIaGPzat0F7bAm46dhPQ94noC1ZdptBpPRO8T24Rl9fukdHXzpmrPRMCuKGdVmuHAKrimHQNE5tS1UtEghOjmTErLjU3rKurDoXsugBePgs4wBykG1TpfZRZp921nD4ki3o1c0SFaasoCYT7Pz04tO+ut/VtBaszLo8toD9dP/3CWihsUozHH6fnjRCyCAsIRTfHVqwHkw/d0O8v0eXTuj31oPq5gPs/KzfqWX69pkkruoUyJCPFaI5oBc4rImhkOG5IZ6Et+o3aaosRkg+J+DCh9waz63UKG98h70ibtexSvGAtpqteZChwlMOjFsWohiDuXc7a3JztEJFKtZWaM7VxoHOTzMB+rkV6t2c4sU9skLj10/D3ydAYK6c5VXMhbJjARcID/bqBD0D+xTGC9o3+UBG/Ydl0NM5PgAAAKDfQPEBAAAYKqD4AAAADBWB5/gWFhZ0TbrAHF9ywPxGvKB9kw9k1H9YBoEU37niG/poOoHiSwa46eMF7Zt8IKP+E1jxAQAAAIMA6zpfxfed73xHHwEAAADSDtH/DyOpF47FEOTNAAAAAElFTkSuQmCC

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • RonKyle

    SSC-Dedicated

    Points: 31459

    CONVERT(VARBINARY(4000),CONVERT(BINARY(4),StaffID))

    The code you provided looks very useful and thorough.  But why is the varbinary set so high?  Why binary at all?

  • Larry Gilbert-474089

    Valued Member

    Points: 74

    Where's the correct staff table? The one listed has no StaffID or ManagerID.

  • Jeff Moden

    SSC Guru

    Points: 994951

    RonKyle - Thursday, August 30, 2018 7:29 AM

    CONVERT(VARBINARY(4000),CONVERT(BINARY(4),StaffID))

    The code you provided looks very useful and thorough.  But why is the varbinary set so high?  Why binary at all?

    If you're talking to me, the answer is two fold...

    1. Storing a character based representation of the IDs is wasteful and slow, especially for what I use the "sortpath" for.
    2. Storing as varbinary allows each integer for the IDs to be stored/concatenated as a very predictable concatenation of BINARY(4) elements which makes for a very fast split of the hierarchical path to very quickly calculate the number of nodes in the "downline" of any given node for the very high performance conversion to nested sets and other calculations.

    As for why VARBINARY(4000), make it anything you want.   I used 4,000 (4000/4 = 1,000 levels) to make sure that most people would never run out room for levels between the given node and the root.  Considering that the hierarchical BOM for a 747 is only about 18 levels deep, 1,000 levels is overkill for most applications except MLM companies. 

    Please see the following articles for more information on the on how I handle DAGs (Directed Acyclic Graphs {Hierarchies}).  The two articles include one of the fastest methods ever developed for conversion from Adjacency Lists to Nested Sets and a very high performance method for doing most of the things people would ask of a hierarchy and storing it all in a pre-aggregated reference table.  To whet your appetite, both methods resolve a million node hierarchy in about 54 seconds, allow for the continued ease of maintenance through an Adjacency List, calculates the hierarchical path in the VARBINARY "sort path" column (for whatever you may need such a thing for), and creates the Nested Sets "bowers", all in the same table.

    Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets
    Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • David Rueter

    SSCrazy

    Points: 2631

    RonKyle - Thursday, August 30, 2018 7:29 AM

    CONVERT(VARBINARY(4000),CONVERT(BINARY(4),StaffID))

    The code you provided looks very useful and thorough.  But why is the varbinary set so high?  Why binary at all?

    Each level of recursion will concatenate additional data to the SortPath, so the length of SortPath will grow depending on the number of levels in the hierarchical data.  This is why the query needs to anticipate long values for SortPath ("why varbinary set so high").

    Assuming StaffID is an int, it is 4 bytes long.  This needs to be concatenated to form SortPath.  So the query either needs to treat StaffID as binary, or to convert it to a string representation.  4 bytes is much shorter than the 11 string characters it would take to represent the longest possible int value (-2147483648)  This is "why binary at all".

    Another approach that I tend to prefer would be to use ROW_NUMBER() to generate a sequential value, and then cast that to as string and concatenate that with a delimiter.  I call this a HUID (hierarchical unique identifier).  It ends up being something like '3.2.1.2' or whatever:  3rd row in the first level of the hierarchy, 2nd row in the second level, 1st row in the third level, and so on.  Padding those sequential values allows for a Sort HUID, such as '0003.0002.0001.0002'.

    Whether it is better to cast a column that is part of the native data or to generate a synthetic sequence number is probably a matter of personal taste and the nature of the native data.

  • RonKyle

    SSC-Dedicated

    Points: 31459

    Jeff Moden - Thursday, August 30, 2018 8:04 AM

    RonKyle - Thursday, August 30, 2018 7:29 AM

    CONVERT(VARBINARY(4000),CONVERT(BINARY(4),StaffID))

    The code you provided looks very useful and thorough.  But why is the varbinary set so high?  Why binary at all?

    If you're talking to me, the answer is two fold...

    1. Storing a character based representation of the IDs is wasteful and slow, especially for what I use the "sortpath" for.
    2. Storing as varbinary allows each integer for the IDs to be stored/concatenated as a very predictable concatenation of BINARY(4) elements which makes for a very fast split of the hierarchical path to very quickly calculate the number of nodes in the "downline" of any given node for the very high performance conversion to nested sets and other calculations.

    As for why VARBINARY(4000), make it anything you want.   I used 4,000 (4000/4 = 1,000 levels) to make sure that most people would never run out room for levels between the given node and the root.  Considering that the hierarchical BOM for a 747 is only about 18 levels deep, 1,000 levels is overkill for most applications except MLM companies. 

    Please see the following articles for more information on the on how I handle DAGs (Directed Acyclic Graphs {Hierarchies}).  The two articles include one of the fastest methods ever developed for conversion from Adjacency Lists to Nested Sets and a very high performance method for doing most of the things people would ask of a hierarchy and storing it all in a pre-aggregated reference table.  To whet your appetite, both methods resolve a million node hierarchy in about 54 seconds, allow for the continued ease of maintenance through an Adjacency List, calculates the hierarchical path in the VARBINARY "sort path" column (for whatever you may need such a thing for), and creates the Nested Sets "bowers", all in the same table.

    Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets
    Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

    I was talking to you, although the subsequent poster also provided useful information.  This is all very interesting, and you have indeed whet my appetite for trying some of this on some recursions that we do here.  Thanks!

  • Jeff Moden

    SSC Guru

    Points: 994951

    Larry Gilbert-474089 - Thursday, August 30, 2018 7:38 AM

    Where's the correct staff table? The one listed has no StaffID or ManagerID.

    If you go to the bottom of the article, you'll find a "Resources" section with a ZIP file in it that Adam was kind enough to provide for all of his articles.  That ZIP file contains all of the DDL to create the tables that his articles reference and the DML to populate them.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Sean.Kelly 93200

    SSC Rookie

    Points: 37

    And why hardcode the 'Finance'? 

    Just pass in a parameter @Dept and change the WHERE clause:

    WHERE   STF.Department = @Dept or @Dept = ''.

    I am creating a CTE for a Personnel/Staffing model and appreciate their flexibility.

  • Brad Schulz

    Old Hand

    Points: 395

    Just to spice things up, check out this blog post I wrote back in 2010 (sheesh, was it THAT long ago??) that will display a hierarchy in a graphical way:
    http://bradsruminations.blogspot.com/2010/04/t-sql-tuesday-005-reporting.html

  • Mike Is Here

    Hall of Fame

    Points: 3348

    If this is the data you have to work with then it is a nice elegant solution 

    But when dealing with hierarchical data I prefer the Nested Set Model
    Put forth by Joe Celko. 
    I used this model in a previous life to relate countries -> manufacturing plants -> Assembly Lines -> Products built.
    I think it worked really well especially when it came to reporting.  It is flexible enough to report on any level.

    https://en.wikipedia.org/wiki/Nested_set_model

  • RonKyle

    SSC-Dedicated

    Points: 31459

    Mike Is Here - Friday, August 31, 2018 7:26 AM

    If this is the data you have to work with then it is a nice elegant solution 

    But when dealing with hierarchical data I prefer the Nested Set Model
    Put forth by Joe Celko. 
    I used this model in a previous life to relate countries -> manufacturing plants -> Assembly Lines -> Products built.
    I think it worked really well especially when it came to reporting.  It is flexible enough to report on any level.

    https://en.wikipedia.org/wiki/Nested_set_model

    This method seems like it has a lot of drawbacks.  Even the article states the use case is rare.

  • Sean.Kelly 93200

    SSC Rookie

    Points: 37

    Brad Schulz - Thursday, August 30, 2018 4:48 PM

    Just to spice things up, check out this blog post I wrote back in 2010 (sheesh, was it THAT long ago??) that will display a hierarchy in a graphical way:
    http://bradsruminations.blogspot.com/2010/04/t-sql-tuesday-005-reporting.html

    Gosh.  So simple but really useful!  Thanks.

  • Mike Is Here

    Hall of Fame

    Points: 3348

    RonKyle - Friday, August 31, 2018 7:37 AM

    This method seems like it has a lot of drawbacks.  Even the article states the use case is rare.

    Well that is wikipedia for you.  I can't link to the book but this blog argues the opposite that the adjacency model has more limitations.
    I don't agree with the part of referential integrity stated in the wiki.  I just don't find that to be the case, if a node does not fit a particular data set then it would have no rows in the corresponding table.

    The issue with multiple inheritance is rare and technically could be handle in the nested model as long as the parent of the two types are not queried.  So in the example you can put Oak under trees and wood types and that would work as long as you don't query Objects which have Trees and Wood-Types, that would double count Oaks.
    But at that point I would create two hierarchical tables at that point since they represent two different subjects 
    Trees would be in a 'Source Type' hierarchical table and Wood-Types in a 'Material Type' hierarchical table

    I actually worked through this problem now that I am typing this out.  We had parts that were pre-assembled in the UK and assembled into a bigger machine in the USA.  This was not a problem until we try to report on the entire company (rarely done) that duplication of that part was an issue but even then you would aggregate the data on a part level then take distinct.

  • Jeff Moden

    SSC Guru

    Points: 994951

    Mike Is Here - Friday, August 31, 2018 9:10 AM

    RonKyle - Friday, August 31, 2018 7:37 AM

    This method seems like it has a lot of drawbacks.  Even the article states the use case is rare.

    Well that is wikipedia for you.  I can't link to the book but this blog argues the opposite that the adjacency model has more limitations.
    I don't agree with the part of referential integrity stated in the wiki.  I just don't find that to be the case, if a node does not fit a particular data set then it would have no rows in the corresponding table.

    The issue with multiple inheritance is rare and technically could be handle in the nested model as long as the parent of the two types are not queried.  So in the example you can put Oak under trees and wood types and that would work as long as you don't query Objects which have Trees and Wood-Types, that would double count Oaks.
    But at that point I would create two hierarchical tables at that point since they represent two different subjects 
    Trees would be in a 'Source Type' hierarchical table and Wood-Types in a 'Material Type' hierarchical table

    I actually worked through this problem now that I am typing this out.  We had parts that were pre-assembled in the UK and assembled into a bigger machine in the USA.  This was not a problem until we try to report on the entire company (rarely done) that duplication of that part was an issue but even then you would aggregate the data on a part level then take distinct.

    The whole problem is the misunderstanding that people have about how to handle hierarchical data when Hierarchical data needs to be handled in an RDBMS...  There are 3 basic types of Hierarchical structures for "DAGs" (Directed Acyclic Graphs) where each node has one and only one parent.  Those are the Adjacency List, the Hierarchical Path, and Nested Sets.  Each has advantages and each has some serious disadvantages.

    1.  Adjacency Lists
    == Easy for humans to maintain and troubleshoot. Change just one manager ID to move a whole sub-tree, for example.
    == Not so easy to aggregate or do other things with.
    == Fairly slow because of it's near RBAR nature (not totally RBAR because it works with one iteration per level returned, if done correctly)
    == Easy and lightweight to index.

    2.  Hierarchical Path  (this is what the HierarchyID datatype is based on and comes in two flavors... positional concatenationand content concatenation).
    == Because each node is "aware" of it's entire upline, very difficult for humans to maintain and troubleshoot.
    == Requires special code to interpret the hierarchical path to return ancestors and descendants and can be horribly slow, which is why MS wrote CLRs for HierarchyID usage.  Without such special code, most humans would struggle with this type of hierarchical structure.
    == Can be difficult to aggregate or do other things with.
    == Requires some not so easy and not so lightweight indexes to get good performance with.
    == Easy and fastest to calculate uplines from because the hierarchical path contains the IDs of every node in the upline.

    3.  Nested Sets
    == Because each node is "aware" of ALL the other nodes in the entire hierarchy, this type of hierarchical structure is very difficult to maintain and humans mostly don't stand a prayer of troubleshooting it if something goes out of whack).
    == Incredibly easy and nasty fast to do a myriad of things including aggregates, etc.  Almost everything is nasty fast set based code with the only exception being the upline.
    == Unless you know the "key" for how to do it in a very high performance manner, Nested Sets can take literally days to rebuild from an Adjacency List (about 2 days for a lousy million nodes for BOMs, MLMs, etc.  Worse yet, the typical "push stack" method for doing such a thing is a terrible resource hog.  Because it takes so long to resolve the hierarchy, additional special code has to be written to add, move, or delete nodes.  One slip an kiss it all goodbye.

    And that's the problem.... none of the 3 structures are a panacea.  So, you actually need all 3.and that's what the two articles I previously provided the links for provide.  They also provide a method for created both the Hierarchical Path and the Nested Sets for a million node hierarchy in 54 seconds.

    Then, there's the "pre-aggregated" hierarch structure that I introduced in the second of the two links I provided and it also does a full rebuild with most everything you could ask of a hierarchy already answered in the table (think hierarchical data warehouse) in only 54 seconds.

    Don't shortcut yourself by settling on just one hierarchical structure.   Use all 3 or 4.  It takes remarkably little time now after we got rid of the highly RBAR push-stack method of creating Nested Sets.

    Maintain just the Adjacency List and let the code do the rest for you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

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

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