SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #Test
( Id INT IDENTITY(1,1)
 ,Person    INT
 ,Job       INT
 ,JobStart  DATE
 ,JobEnd    DATE
 ,Modified  DATE
);
INSERT #Test (Person, Job, JobStart, JobEnd, Modified)
VALUES
 (1,1,'2020-01-10','2020-01-20',GETDATE()) 
,(1,2,'2020-01-03','2020-01-10',DATEADD(DAY,-1,GETDATE())) 
,(1,3,'2020-01-03','2020-01-13',DATEADD(DAY,-2,GETDATE())) 
,(1,4,'2020-01-11','2020-01-20',DATEADD(DAY,-3,GETDATE())) 
,(1,5,'2020-01-15','2020-01-25',DATEADD(DAY,-4,GETDATE())) 
,(1,6,'2020-01-01','2020-01-30',DATEADD(DAY,-5,GETDATE()));
SELECT * FROM #Test ORDER BY Person, JobStart, JobEnd;


Id
Person
Job
JobStart
JobEnd
Modified


6
1
6
2020-01-01
2020-01-30
2023-01-22

2
1
2
2020-01-03
2020-01-10
2023-01-26

3
1
3
2020-01-03
2020-01-13
2023-01-25

1
1
1
2020-01-10
2020-01-20
2023-01-27

4
1
4
2020-01-11
2020-01-20
2023-01-24

5
1
5
2020-01-15
2020-01-25
2023-01-23


WITH RCTE_DATES AS (
   SELECT MIN(JobStart) AS JobDate, MAX(JobEnd) AS LastDate
   FROM #Test
   UNION ALL
   SELECT DATEADD(day, 1, JobDate), LastDate
   FROM RCTE_DATES
   WHERE JobDate < LastDate
)
, CTE_RANGES AS
(
   SELECT JobDate, Person, Job, JobStart, JobEnd
   , ROW_NUMBER() OVER (PARTITION BY Person, JobDate ORDER BY Modified DESC, JobEnd) rn
   FROM RCTE_DATES d
   JOIN #Test t ON d.JobDate BETWEEN t.JobStart AND t.JobEnd
)
, CTE_RANKED AS
(
   SELECT JobDate, Person, Job
   , ROW_NUMBER() OVER (PARTITION BY Person, Job ORDER BY JobDate DESC) 
   + ROW_NUMBER() OVER (ORDER BY JobDate) AS Rnk
   FROM CTE_RANGES
   WHERE rn = 1
   GROUP BY JobDate, Person, Job
)
SELECT Person, Job
, MIN(JobDate) AS JobStart
, MAX(JobDate) AS JobEnd
FROM CTE_RANKED
GROUP BY Person, Job, Rnk
ORDER BY JobStart


Person
Job
JobStart
JobEnd


1
6
2020-01-01
2020-01-02

1
2
2020-01-03
2020-01-09

1
1
2020-01-10
2020-01-20

1
5
2020-01-21
2020-01-25

1
6
2020-01-26
2020-01-30


--
-- High Plains Grifter's attempt
--
WITH ListOfDates AS (
   SELECT MIN(JobStart) AS [Date], MAX(JobEnd) AS LastDate
   FROM #Test
   UNION ALL
   SELECT DATEADD(day, 1, [Date]), LastDate
   FROM ListOfDates
   WHERE [Date] < LastDate
)
, AllDays AS 
(   SELECT
         Person
        ,Dated.[Date]
        ,MAX(Modified) AS LastModified
    FROM        #Test       AS Test
    INNER JOIN  ListOfDates AS Dated    ON  Test.JobStart <= Dated.[Date]
                                                    AND Test.JobEnd >= Dated.[Date]
    GROUP BY Person,[Date]
)
,Thing AS
(
SELECT
     Test.Person
    ,Test.Job
    ,MIN([Date]) OVER (PARTITION BY Test.Job ORDER BY [Date])   AS JobStart
    ,MAX([Date]) OVER (PARTITION BY Test.Job ORDER BY [Date])   AS JobEnd
FROM
            #Test   AS Test
INNER JOIN  AllDays AS Limit    ON  Test.Person = Limit.Person
                                AND Test.Modified = Limit.LastModified
)
SELECT *
FROM Thing


Person
Job
JobStart
JobEnd


1
1
2020-01-10
2020-01-10

1
1
2020-01-10
2020-01-11

1
1
2020-01-10
2020-01-12

1
1
2020-01-10
2020-01-13

1
1
2020-01-10
2020-01-14

1
1
2020-01-10
2020-01-15

1
1
2020-01-10
2020-01-16

1
1
2020-01-10
2020-01-17

1
1
2020-01-10
2020-01-18

1
1
2020-01-10
2020-01-19

1
1
2020-01-10
2020-01-20

1
2
2020-01-03
2020-01-03

1
2
2020-01-03
2020-01-04

1
2
2020-01-03
2020-01-05

1
2
2020-01-03
2020-01-06

1
2
2020-01-03
2020-01-07

1
2
2020-01-03
2020-01-08

1
2
2020-01-03
2020-01-09

1
5
2020-01-21
2020-01-21

1
5
2020-01-21
2020-01-22

1
5
2020-01-21
2020-01-23

1
5
2020-01-21
2020-01-24

1
5
2020-01-21
2020-01-25

1
6
2020-01-01
2020-01-01

1
6
2020-01-01
2020-01-02

1
6
2020-01-01
2020-01-26

1
6
2020-01-01
2020-01-27

1
6
2020-01-01
2020-01-28

1
6
2020-01-01
2020-01-29

1
6
2020-01-01
2020-01-30

Id	Person	Job	JobStart	JobEnd	Modified
6	1	6	2020-01-01	2020-01-30	2023-01-22
2	1	2	2020-01-03	2020-01-10	2023-01-26
3	1	3	2020-01-03	2020-01-13	2023-01-25
1	1	1	2020-01-10	2020-01-20	2023-01-27
4	1	4	2020-01-11	2020-01-20	2023-01-24
5	1	5	2020-01-15	2020-01-25	2023-01-23

Person	Job	JobStart	JobEnd
1	6	2020-01-01	2020-01-02
1	2	2020-01-03	2020-01-09
1	1	2020-01-10	2020-01-20
1	5	2020-01-21	2020-01-25
1	6	2020-01-26	2020-01-30