hierarchy related query

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  September 7, 2021 at 12:26 pm

  #3927471

  Hello friends.

  I have a hierarchy table like this:

  Month empEntityID empID empName mgrID empEntityID1 empID1 empName1 empEntityID2 empID2 empName2 empEntityID3 empID3 empName3 empEntityID4 empID4 empName4 empEntityID5 empID5 empName5 empEntityID6 empID6 empName6

  Month contains first calendar date of a month which shows which month a hierarchy set belongs to.

  Every employee is mentioned in first three  fields (empEntityID empID empName). Immediate manager is mentioned in mgrID.

  Rest all is the hierarchy from topmost person at (empEntityID1 empID1 empName1) to till the immediate manager in the hierarchy. rest all will be null.

  Now I am working on a requirement where I have to compare two months data to analyze what were the changes in the hierarchy like:

  a. which new employees joined and which left (that i have achieved using full outer join)

  b. employees movement in hierarchy (employees moved to different manager at same level or promoted to higher level or demoted to lower level). Also need the old manager and new manager information. This is the requirement where I am struggling.

  If anyone can help me here with point B. I will be really thankful.

   

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 7, 2021 at 3:20 pm

  #3927541

  You've been around long enough to know and have been advised before (April 2020) that you need to post some real DDL for such problems.  Please read'n'heed the article at the first link in my signature line below.

  --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.

  Change is inevitable... Change for the better is not.
  

  ---

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

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  September 7, 2021 at 5:43 pm

  #3927584

  My bad Jeff. Apologies and i am making up for that mistake by making you CEO of the example. 🙂 Here is the code:

  This is the hierarchy table:

  create table dbo.EmpHierarchy
  (HierMonth datetime, 
  empEntityID int, empID varchar(10), empName varchar(255), mgrID varchar(10),
  empEntityID1 int, empID1 varchar(10), empName1 varchar(255),
  empEntityID2 int, empID2 varchar(10), empName2 varchar(255),
  empEntityID3 int, empID3 varchar(10), empName3 varchar(255),
  empEntityID4 int, empID4 varchar(10), empName4 varchar(255),
  empEntityID5 int, empID5 varchar(10), empName5 varchar(255),
  empEntityID6 int, empID6 varchar(10), empName6 varchar(255),
  empEntityID7 int, empID7 varchar(10), empName7 varchar(255),
  empEntityID8 int, empID8 varchar(10), empName8 varchar(255),
  empEntityID9 int, empID9 varchar(10), empName9 varchar(255))

  Here is the sample data:

  insert into dbo.EmpHierarchy
  select '2021-08-01', 1, '1001', 'Jeff Moden', '-1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 2, '1002', 'Robert Bravo', '1001', 1, '1001', 'Jeff Moden', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 3, '1003', 'Katie Berry', '1001', 1, '1001', 'Jeff Moden', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 4, '1004', 'Rajesh Kuthrapally', '1002', 1, '1001', 'Jeff Moden', 2, '1002', 'Robert Bravo', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 6, '1006', 'Sam Billings', '1003', 1, '1001', 'Jeff Moden', 3, '1003', 'Katie Berry', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 8, '1008', 'Michael Deuchar', '1003', 1, '1001', 'Jeff Moden', 3, '1003', 'Katie Berry', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
  

  Now this data is only for August and a limited one off course. Now if next month's hierarchy comes with the changes I mentioned, there I need to validate the data changes. This part:

  b. employees movement in hierarchy (employees moved to different manager at same level or promoted to higher level or demoted to lower level). Also need the old manager and new manager information. This is the requirement where I am struggling.

  • This reply was modified 2 years, 7 months ago by  sqlenthu 89358.

• pietlinden

  SSC Guru

  Points: 63421

  More actions

  September 7, 2021 at 7:18 pm

  #3927611

  Normalization 101 time, I think. that's a LOT of repeating fields (any time I see a column name with a number at the end, I'm immediately suspect).

  Is this supposed to be an {EmployeeID, ManagerID} hierarchy? If an employee can only have one manager, then it's

  CREATE TABLE Employee (EmployeeID INT IDENTITY PRIMARY KEY, ManagerID INT DEFAULT NULL)... foreign key references Employee(EmployeeID).

  This isn't super different from "Find all the prerequisites [supervisees] for course [manager] X" I asked that question a long time ago... the discussion is here - same hierarchical query.

  • This reply was modified 2 years, 7 months ago by  pietlinden.
  • This reply was modified 2 years, 7 months ago by  pietlinden.

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 8, 2021 at 6:16 am

  #3927690

  Do you have the next or previous month worth of test data that we can load to do some testing with?

  --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.

  Change is inevitable... Change for the better is not.
  

  ---

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

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  September 8, 2021 at 10:21 am

  #3927768

  Lets say this is the second month data with some changes in hierarchy:

  insert into dbo.EmpHierarchy
  select '2021-09-01', 1, '1001', 'Jeff Moden', '-1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-09-01', 2, '1002', 'Robert Bravo', '1001', 1, '1001', 'Jeff Moden', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-09-01', 3, '1003', 'Katie Berry', '1001', 1, '1001', 'Jeff Moden', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-09-01', 4, '1004', 'Rajesh Kuthrapally', '1002', 1, '1001', 'Jeff Moden', 2, '1002', 'Robert Bravo', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-09-01', 6, '1006', 'Sam Billings', '1003', 1, '1001', 'Jeff Moden', 3, '1003', 'Katie Berry', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-09-01', 9, '1010', 'Ramon Ray', '1002', 1, '1001', 'Jeff Moden', 2, '1002', 'Robert Bravo', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-09-01', 10, '1011', 'Joseph Alberto', '1001', 1, '1001', 'Jeff Moden', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-09-01', 11, '1012', 'Veronica McCarthy', '1011', 1, '1001', 'Jeff Moden', 10, '1011', 'Joseph Alberto', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-09-01', 8, '1008', 'Michael Deuchar', '1011', 1, '1001', 'Jeff Moden', 10, '1011', 'Joseph Alberto', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL                 
  

  Here the changes are as follows:

  1. New employees came with empId 1010, 1011, 1012
  2. One employee hierarchy changed. emp ID 1008 reporting changed from 1003 to 1011.

  So I need to get data like

  HireMonthempEntityIdempIdempNameimpactemp Impacted
  2021-09-0191010Ramon Raynew addition1002
  2021-09-01101011Joseph Albertonew addition1001
  2021-09-01111012Veronica McCarthynew addition1011
  2021-09-0181008Michael Deucharemp moved out1003
  2021-09-0181008Michael Deucharemp moved in1011

   

• Jonathan AC Roberts

  SSCoach

  Points: 18257

  More actions

  September 8, 2021 at 11:17 am

  #3927776

  This returns the results you have given:

  declare @month1 datetime = '2021-08-01 00:00:00.000',
          @month2 datetime = '2021-09-01 00:00:00.000'
  
  select e.HierMonth, e.empID,empName,'new addition',mgrID
    from dbo.EmpHierarchy e
   where HierMonth = @month2
     and not exists(select *
                      from dbo.EmpHierarchy e2
                     where e2.HierMonth = @month1
                       and e2.empID = e.empID)
   union all
  select e.HierMonth, e.empID,empName,'moved out',mgrID
    from dbo.EmpHierarchy e
   where HierMonth = @month1
     and exists(select *
                  from dbo.EmpHierarchy e2
                 where e2.HierMonth = @month2
                   and e2.empID = e.empID
                   and e2.mgrID <> e.mgrID)
   union all
  select e.HierMonth, e.empID,empName,'moved in',mgrID
    from dbo.EmpHierarchy e
   where HierMonth = @month2
     and exists(select *
                  from dbo.EmpHierarchy e2
                 where e2.HierMonth = @month1
                   and e2.empID = e.empID
                   and e2.mgrID <> e.mgrID)

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 8, 2021 at 1:18 pm

  #3927856

  @SQLEnthu 89358

  Isn't it amazing how quick people can provide an answer when they actually have data to help them understand the problems and they have a place to test some code? 😀

  Well done on the readily consumable data.

  --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.

  Change is inevitable... Change for the better is not.
  

  ---

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

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  September 10, 2021 at 11:43 am

  #3928520

  Really that's good. And don't know why i couldn't think of except clause (maybe overthinking).

  Apologies but i have another set of question. Query actually is little different as i am more understanding the problem now (please forgive me for that)

  Now I have another table where I maintain a separate custom hierarchy, for any xyz work (i know its a bad architecture but lets not discuss that aspect as its a very old system full of crap). This new hierarchy is created with the help of selecting employees from the EmployeeHierarhcy. The table is like this:

  create table dbo.EmpCustomHierarchy
  (HierMonth datetime, hier_id int, HierName varchar(50), ListOfEmployees varchar(max))
  
  insert into dbo.EmpCustomHierarchy
  select '2021-08-01', 1, 'Alpha', '1004,1006' union all
  select '2021-08-01', 2, 'Delta', '1008'
  

  Level 3 employees can be selected for a hierarchy in way that if you are selecting all the employees at level 3 then the hierarchy will have LisofEmployees as level 2 employee + all level 3 employees (comma separated). However if there's partial selection of level 3 employees in the custom hierachy then only those selected level 3 will be added to hierarchy. All the level 3 employees will be part of one of the hierarchy.

  Now when Employee hierarchy comes for 01-Sep, then there might be changes in hierarchies as i gave the example. Based on those changes i have to see which of these custom hierarchies are getting impacted. If there's any new employee joining at level 3 in employee hierarchy then we need to check if it should be part of any custom hierarchies which contains level 3 of same parent as the new one.

  Same way also need to find it any employees moved from one manager at level 2 to new manager at level 2.

  Ideally need to identify such changes in custom hierarchy & mark them as impacted based on changes in employee hierarchy. Not sure if any of you will be getting it.

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  September 27, 2021 at 1:27 pm

  #3933480

  sqlenthu 89358 wrote:

  My bad Jeff. Apologies and i am making up for that mistake by making you CEO of the example. 🙂 Here is the code:

  This is the hierarchy table:

  create table dbo.EmpHierarchy
  (HierMonth datetime, 
  empEntityID int, empID varchar(10), empName varchar(255), mgrID varchar(10),
  empEntityID1 int, empID1 varchar(10), empName1 varchar(255),
  empEntityID2 int, empID2 varchar(10), empName2 varchar(255),
  empEntityID3 int, empID3 varchar(10), empName3 varchar(255),
  empEntityID4 int, empID4 varchar(10), empName4 varchar(255),
  empEntityID5 int, empID5 varchar(10), empName5 varchar(255),
  empEntityID6 int, empID6 varchar(10), empName6 varchar(255),
  empEntityID7 int, empID7 varchar(10), empName7 varchar(255),
  empEntityID8 int, empID8 varchar(10), empName8 varchar(255),
  empEntityID9 int, empID9 varchar(10), empName9 varchar(255))

  Here is the sample data:

  insert into dbo.EmpHierarchy
  select '2021-08-01', 1, '1001', 'Jeff Moden', '-1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 2, '1002', 'Robert Bravo', '1001', 1, '1001', 'Jeff Moden', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 3, '1003', 'Katie Berry', '1001', 1, '1001', 'Jeff Moden', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 4, '1004', 'Rajesh Kuthrapally', '1002', 1, '1001', 'Jeff Moden', 2, '1002', 'Robert Bravo', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 6, '1006', 'Sam Billings', '1003', 1, '1001', 'Jeff Moden', 3, '1003', 'Katie Berry', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  select '2021-08-01', 8, '1008', 'Michael Deuchar', '1003', 1, '1001', 'Jeff Moden', 3, '1003', 'Katie Berry', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
  

  Now this data is only for August and a limited one off course. Now if next month's hierarchy comes with the changes I mentioned, there I need to validate the data changes. This part:

  b. employees movement in hierarchy (employees moved to different manager at same level or promoted to higher level or demoted to lower level). Also need the old manager and new manager information. This is the requirement where I am struggling.

  Hello everyone,

  With above dataset, how can we get the level of any employee ? like following dataset for 2021-08-01:

  empID empName empPositionId

  1001 Jeff Moden 1

  1002 Robert Bravo 2

  1003 Katie Berry 2

  1004 Rajesh Kuthrapally 3

  1006 Sam Billings 3

  1008 Michael Deuchar 3

  In between thanks for the earlier replies. I was down with fever so couldn't reply.

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 27, 2021 at 3:25 pm

  #3933556

  My belief is that you're doing this whole thing the wrong way.  Have a look at how easy it can be at the following URL.

  https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets

  Depending on your reporting requirements, the 2nd article in that series can also make stuff a whole lot easier.

  https://www.sqlservercentral.com/articles/hierarchies-on-steroids-2-a-replacement-for-nested-sets-calculations-1

  And, on today's machines, both pieces of code take only 19 seconds to run on a MILLION NODE Hierarchy!

  --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.

  Change is inevitable... Change for the better is not.
  

  ---

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