I have asked

My approach:

Develop a temp table (#EmployeeManagersIntermediate) with exploded dates, using the #EmployeeManagersSource table as source:

;
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1)
    ,E02(N) AS (SELECT 1 FROM E00 a, E00 b)
    ,E04(N) AS (SELECT 1 FROM E02 a, E02 b)
    ,E08(N) AS (SELECT 1 FROM E04 a, E04 b)
    ,E16(N) AS (SELECT 1 FROM E08 a, E08 b)
    ,E32(N) AS (SELECT 1 FROM E16 a, E16 b)
    ,cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E32)
    ,DateRange AS
(
    SELECT ExplodedDate = DATEADD(DAY,N - 1,'1960-01-01')
    FROM cteTally
    WHERE N <= 365000
)
SELECT EmployeeName, EmployeeId, ManagerId, ManagerName, StartDate, EndDate, CONVERT(date,ExplodedDate) AS ExplodedDate
INTO #EmployeeManagersIntermediate
FROM #EmployeeManagersSource eh
JOIN DateRange d ON d.ExplodedDate >= eh.[StartDate]
 AND d.ExplodedDate <= eh.[EndDate];
 

 SELECT *
 FROM #EmployeeManagersIntermediate
 WHERE ManagerName = 'Lisa'

However, am not able to get the #EmployeeManagersDesiredOutput using #EmployeeManagersIntermediate, based on this 'Gaps and Islands' problem here.

I think I need a proper PARTITION BY clause. Can someone suggest a solution on how change #EmployeeManagersIntermediate to #EmployeeManagersDesiredOutput ?

CodePudding user response：

You can use the following query:

;WITH Dates AS (
    SELECT EmployeeId, EmployeeName, SomeDate,
        ROW_NUMBER() OVER (PARTITION BY x.EmployeeId ORDER BY x.SomeDate) AS RowNum
    FROM (
        SELECT EmployeeId, EmployeeName, StartDate AS SomeDate
        FROM #EmployeeManagersSource
        UNION
        SELECT EmployeeId, EmployeeName, DATEADD(DAY,1,EndDate)
        FROM #EmployeeManagersSource
    ) x
), Intervals AS (
    SELECT d1.EmployeeId, d1.EmployeeName, 
        d1.SomeDate AS StartDate, DATEADD(DAY,-1,d2.SomeDate) AS EndDate
    FROM Dates d1 
    INNER JOIN Dates d2 
    ON d2.EmployeeId = d1.EmployeeId AND d1.RowNum=d2.RowNum-1
)
SELECT i.EmployeeName, i.EmployeeId, s.ManagerId, s.ManagerName, i.StartDate, i.EndDate
FROM Intervals i
INNER JOIN #EmployeeManagersSource s 
ON s.EmployeeId = i.EmployeeId AND s.StartDate<=i.StartDate AND s.EndDate>=i.EndDate

The first CTE builds a list of dates for when changes appear, the second CTE builds the intervals, the final query finds the mangers for each interval.