Recursive CTE for Supervisor Hierarchy WITH Effective Date of Reporting Relationship - Updated with DDL and Sample Data

• DBAless

  Ten Centuries

  Points: 1196

  More actions

  November 7, 2018 at 11:33 am

  #2012731

  After some more testing, I've found that my test data did not match my expected results, so I fixed the test data.  Also, on the recursive section, it needed to compare the supervisor's effective end to the employee's effective end (similar to the effective start case statement).   Here's where I've ended up.  The case statements in the recursion were the key. 

  declare @EmpID int
select @EmpID = 3

  ;with AllEmployees as
(
select e.EmpID, e.EmpCode, e.ProperName, h.EffectiveStart,
EffectiveEnd = isnull(h.EffectiveEnd,'9999-12-30 00:00:00.000')
from z_Employees e
join z_ReportsToHistory h on e.EmpID = h.EmpID
where e.EmpID = @EmpID
union all
select e.EmpID, e.EmpCode, e.ProperName, 
      -- case statements ensure that the supervisor's date range overrides the employee's date range if the supervisor's range is more narrow
EffectiveStart = case when h.EffectiveStart < d.EffectiveStart then d.EffectiveStart else h.EffectiveStart end, 
EffectiveEnd = case when isnull(h.EffectiveEnd,'9999-12-30 00:00:00.000') > isnull(d.EffectiveEnd,'9999-12-30 00:00:00.000')
     then isnull(d.EffectiveEnd,'9999-12-30 00:00:00.000') else isnull(h.EffectiveEnd,'9999-12-30 00:00:00.000') end
from z_Employees e
join z_ReportsToHistory h on e.EmpID = h.EmpID
join AllEmployees d on h.ReportsToID = d.EmpID
)
select e.EmpID, t.PEDate,
DirectHours = sum(case when p.ProjectType = 1 then i.WorkHours else 0 end),
TotalHours = sum(i.WorkHours)
from AllEmployees e
join z_Timesheets t on e.EmpID = t.EmpID and t.PEDate between e.EffectiveStart and e.EffectiveEnd
join z_TimesheetItems i on t.TimeID = i.TimeID
join z_Projects p on i.ProjectID = p.ProjectID
group by e.EmpID, t.PEDate
order by e.EmpID, t.PEDate

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 7, 2018 at 12:07 pm

  #2012739

  DBAless - Wednesday, November 7, 2018 11:33 AM

  After some more testing, I've found that my test data did not match my expected results, so I fixed the test data.  Also, on the recursive section, it needed to compare the supervisor's effective end to the employee's effective end (similar to the effective start case statement).   Here's where I've ended up.  The case statements in the recursion were the key. 

  declare @EmpID int
select @EmpID = 3

  ;with AllEmployees as
(
select e.EmpID, e.EmpCode, e.ProperName, h.EffectiveStart,
EffectiveEnd = isnull(h.EffectiveEnd,'9999-12-30 00:00:00.000')
from z_Employees e
join z_ReportsToHistory h on e.EmpID = h.EmpID
where e.EmpID = @EmpID
union all
select e.EmpID, e.EmpCode, e.ProperName, 
      -- case statements ensure that the supervisor's date range overrides the employee's date range if the supervisor's range is more narrow
EffectiveStart = case when h.EffectiveStart < d.EffectiveStart then d.EffectiveStart else h.EffectiveStart end, 
EffectiveEnd = case when isnull(h.EffectiveEnd,'9999-12-30 00:00:00.000') > isnull(d.EffectiveEnd,'9999-12-30 00:00:00.000')
     then isnull(d.EffectiveEnd,'9999-12-30 00:00:00.000') else isnull(h.EffectiveEnd,'9999-12-30 00:00:00.000') end
from z_Employees e
join z_ReportsToHistory h on e.EmpID = h.EmpID
join AllEmployees d on h.ReportsToID = d.EmpID
)
select e.EmpID, t.PEDate,
DirectHours = sum(case when p.ProjectType = 1 then i.WorkHours else 0 end),
TotalHours = sum(i.WorkHours)
from AllEmployees e
join z_Timesheets t on e.EmpID = t.EmpID and t.PEDate between e.EffectiveStart and e.EffectiveEnd
join z_TimesheetItems i on t.TimeID = i.TimeID
join z_Projects p on i.ProjectID = p.ProjectID
group by e.EmpID, t.PEDate
order by e.EmpID, t.PEDate

  Reformatted your code and put semicolons where they belong, which is NOT in front of the WITH that defines a CTE.  Semicolons are terminators not beginnators.  They belong at the END of statements.  As not terminating statements with a semicolon has been deprecated you should get in the habit of using them at the end of statements.

  
declare @EmpID int;
select @EmpID = 3;
with [AllEmployees]
as (select [e].[EmpID]
   , [e].[EmpCode]
   , [e].[ProperName]
   , [h].[EffectiveStart]
   , 'EffectiveEnd' = isnull([h].[EffectiveEnd], '9999-12-30 00:00:00.000')
  from [z_Employees] as [e]
   join [z_ReportsToHistory] as [h]
    on [e].[EmpID] = [h].[EmpID]
  where [e].[EmpID] = @EmpID
  union all
  select [e].[EmpID]
   , [e].[EmpCode]
   , [e].[ProperName]
   -- case statements ensure that the supervisor's date range overrides the employee's date range if the supervisor's range is more narrow
   , 'EffectiveStart' = case
            when [h].[EffectiveStart] < [d].[EffectiveStart] then
             [d].[EffectiveStart]
            else
             [h].[EffectiveStart]
          end
   , 'EffectiveEnd' = case
            when isnull([h].[EffectiveEnd], '9999-12-30 00:00:00.000') > isnull(
                                   [d].[EffectiveEnd]
                                   , '9999-12-30 00:00:00.000'
                                  ) then
             isnull([d].[EffectiveEnd], '9999-12-30 00:00:00.000')
            else
             isnull([h].[EffectiveEnd], '9999-12-30 00:00:00.000')
          end
  from [z_Employees] as [e]
   join [z_ReportsToHistory] as [h]
    on [e].[EmpID] = [h].[EmpID]
   join [AllEmployees] as [d]
    on [h].[ReportsToID] = [d].[EmpID]
 )
select [e].[EmpID]
  , [t].[PEDate]
  , 'DirectHours' = sum( case
            when [p].[ProjectType] = 1 then
             .[WorkHours]
            else
             0
          end
         )
  , 'TotalHours' = sum(.[WorkHours])
from [AllEmployees] as [e]
  join [z_Timesheets] as [t]
   on [e].[EmpID] = [t].[EmpID]
    and [t].[PEDate]
    between [e].[EffectiveStart] and [e].[EffectiveEnd]
  join [z_TimesheetItems] as
   on [t].[TimeID] = .[TimeID]
  join [z_Projects] as [p]
   on .[ProjectID] = [p].[ProjectID]
group by [e].[EmpID]
   , [t].[PEDate]
order by [e].[EmpID]
   , [t].[PEDate];


• DBAless

  Ten Centuries

  Points: 1196

  More actions

  November 7, 2018 at 12:37 pm

  #2012741

  Yeah, I have several habits I'll have to adjust when we upgrade from SQL Server 2014.  Of course, the vendor application I'm "tweaking" has a ton of old code like raiserror that will need clean up if MS ever truly commits to all the things they say they are deprecating.

  Thanks again for the help!   Once you got me on the right path it wasn't as hard I was making out to be in my head.  🙂

• DBAless

  Ten Centuries

  Points: 1196

  More actions

  January 28, 2019 at 9:13 am

  #2020291

  I've come across a new scenario that I'm stuck on.  I have two employees that switched places.  It causes the recursion to endlessly loop.  

  
--***************************************************************************************
-- Generate employee data
--***************************************************************************************
CREATE TABLE #Employee (
          EmpID int NOT NULL,
          EmpName nvarchar(255) NOT NULL);

  INSERT INTO #Employee
  VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F'),(7,'G'),(8,'H'),(9,'I'),(10,'J'),
    (11,'K'),(12,'L')

  --***************************************************************************************
  -- Generate supervisory hierachy with historical effective dates
  --***************************************************************************************
  CREATE TABLE #ReportsToHistory(
         EmpID int NOT NULL,
         ReportsToID int NOT NULL,
         EffectiveStart date NOT NULL,
         EffectiveEnd date NULL);

  INSERT INTO #ReportsToHistory
  VALUES (1,1,'2018-11-09',null),
        (2,1,'2018-11-09',null),
        (3,1,'2018-11-09',null),
        (4,2,'2018-11-09',null),
        (5,2,'2018-11-09',null),
        (6,3,'2018-11-09',null),
        (7,2,'2018-11-09','2018-11-16'),
        (7,3,'2018-11-23',null),
        (8,7,'2018-11-09',null),
        (9,6,'2018-11-09','2018-11-09'),
        (9,3,'2018-11-16','2018-11-16'),
        (9,2,'2018-11-23',null),
        (10,4,'2018-11-09','2018-11-09'),
        (10,5,'2018-11-16',null),
     (11,1,'2018-11-09','2018-11-09'),
     (11,12,'2018-11-16',null),
     (12,11,'2018-11-09','2018-11-09'),
     (12,1,'2018-11-16',null);

  --***************************************************************************************
  -- Result Set For Reports
  --***************************************************************************************

  with AllEmployees as
  (
  select e.EmpID, e.EmpName,
  RepToID = cast(0 as int), RepToName = cast('' as nvarchar(255)),
  EffectiveStart = cast('1900-01-05' as datetime), EffectiveEnd = cast('9999-12-30' as datetime)
  from #Employee e
  where e.EmpID = 1
  union all
  select e.EmpID, e.EmpName, h.ReportsToID, m.EmpName,
  EffectiveStart = case when h.EffectiveStart < d.EffectiveStart then d.EffectiveStart else h.EffectiveStart end,
  EffectiveEnd = case when isnull(h.EffectiveEnd,'9999-12-30') > isnull(d.EffectiveEnd,'9999-12-30')
       then isnull(d.EffectiveEnd,'9999-12-30') else isnull(h.EffectiveEnd,'9999-12-30') end
  from #Employee e
  join #ReportsToHistory h on e.EmpID = h.EmpID
  join #Employee m on h.ReportsToID = m.EmpID
  join AllEmployees d on h.ReportsToID = d.EmpID
  where e.EmpID <> 1
  )
  select *
  from AllEmployees;

  --***************************************************************************************
  -- Clean Up
  --***************************************************************************************

  drop table #Employee;
  drop table #ReportsToHistory;