SQL Server 2017

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

create table tbl (Employee int, Position int, StartDate Date, [EndDate] Date, PosType char(1));
insert into tbl values
(1, 30, '20161210', '20161211', 'P'),
(1, 30, '20161212', '20171208', 'P'),
(1, 20, '20170131', '20170202', 'A'),
(1, 20, '20170329', '20170331', 'A'),
(1, 20, '20170419', '20170421', 'A'),
(1, 20, '20170622', '20170623', 'A'),
(1, 20, '20170629', '20170630', 'A'),
(1, 20, '20171003', '20171006', 'A'),
(1, 30, '20171209', '20180726', 'P'),
(1, 20, '20180212', '20180302', 'A'),
(1, 20, '20180719', '20180720', 'A'),
(1, 30, '20180727', '20180727', 'P'),
(1, 30, '20180728', '99991231', 'P'),
(1, 20, '20180904', '20180905', 'A'),
(1, 20, '20181112', '20181113', 'A'),
(1, 20, '20181119', '20181120', 'A');
16 rows affected

SELECT   t1.*,
         ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) Id
FROM     tbl t1
WHERE    t1.PosType = 'P'
ORDER BY t1.Employee, t1.StartDate
;


Employee
Position
StartDate
EndDate
PosType
Id


1
30
10/12/2016 00:00:00
11/12/2016 00:00:00
P
1

1
30
12/12/2016 00:00:00
08/12/2017 00:00:00
P
2

1
30
09/12/2017 00:00:00
26/07/2018 00:00:00
P
3

1
30
27/07/2018 00:00:00
27/07/2018 00:00:00
P
4

1
30
28/07/2018 00:00:00
31/12/9999 00:00:00
P
5


SELECT   t2.*,
         ROW_NUMBER() OVER (ORDER BY t2.StartDate) SubId
FROM     tbl t2
WHERE    t2.PosType = 'A'
         AND t2.StartDate >= '20161212'
         AND t2.StartDate <= '20171208'
ORDER BY t2.Employee, t2.StartDate
;


Employee
Position
StartDate
EndDate
PosType
SubId


1
20
31/01/2017 00:00:00
02/02/2017 00:00:00
A
1

1
20
29/03/2017 00:00:00
31/03/2017 00:00:00
A
2

1
20
19/04/2017 00:00:00
21/04/2017 00:00:00
A
3

1
20
22/06/2017 00:00:00
23/06/2017 00:00:00
A
4

1
20
29/06/2017 00:00:00
30/06/2017 00:00:00
A
5

1
20
03/10/2017 00:00:00
06/10/2017 00:00:00
A
6


SELECT   t1.*, COALESCE(t3.P2, 0) P2, P, t3.PrevED, t3.SD, t3.ED, NextSD, t3.PT
INTO #mydata
FROM     (SELECT *,
                 ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) as P1
          FROM   tbl 
          WHERE  PosType = 'P'
          ) t1
OUTER APPLY (SELECT   ROW_NUMBER() OVER (ORDER BY t2.StartDate) as P2,
                      LAG(t2.EndDate) OVER (ORDER BY t2.StartDate) as PrevED,
                      LEAD(t2.StartDate) OVER (ORDER BY t2.StartDate) as NextSD,
                      t2.Position as P, t2.StartDate as SD, t2.EndDate as ED, t2.PosType as PT
             FROM     tbl t2
             WHERE    t2.Employee = t1.Employee
                      AND t2.PosType = 'A'
                      AND t2.StartDate >= t1.StartDate
                      AND t2.StartDate <= t1.EndDate
            ) t3
WHERE    t1.PosType = 'P'
ORDER BY t1.Employee, t1.StartDate, t3.SD
;

CREATE CLUSTERED INDEX PK_Mydata ON #mydata(Employee, P1, P2);

SELECT * FROM #mydata;


Employee
Position
StartDate
EndDate
PosType
P1
P2
P
PrevED
SD
ED
NextSD
PT


1
30
10/12/2016 00:00:00
11/12/2016 00:00:00
P
1
0
null
null
null
null
null
null

1
30
12/12/2016 00:00:00
08/12/2017 00:00:00
P
2
1
20
null
31/01/2017 00:00:00
02/02/2017 00:00:00
29/03/2017 00:00:00
A

1
30
12/12/2016 00:00:00
08/12/2017 00:00:00
P
2
2
20
02/02/2017 00:00:00
29/03/2017 00:00:00
31/03/2017 00:00:00
19/04/2017 00:00:00
A

1
30
12/12/2016 00:00:00
08/12/2017 00:00:00
P
2
3
20
31/03/2017 00:00:00
19/04/2017 00:00:00
21/04/2017 00:00:00
22/06/2017 00:00:00
A

1
30
12/12/2016 00:00:00
08/12/2017 00:00:00
P
2
4
20
21/04/2017 00:00:00
22/06/2017 00:00:00
23/06/2017 00:00:00
29/06/2017 00:00:00
A

1
30
12/12/2016 00:00:00
08/12/2017 00:00:00
P
2
5
20
23/06/2017 00:00:00
29/06/2017 00:00:00
30/06/2017 00:00:00
03/10/2017 00:00:00
A

1
30
12/12/2016 00:00:00
08/12/2017 00:00:00
P
2
6
20
30/06/2017 00:00:00
03/10/2017 00:00:00
06/10/2017 00:00:00
null
A

1
30
09/12/2017 00:00:00
26/07/2018 00:00:00
P
3
1
20
null
12/02/2018 00:00:00
02/03/2018 00:00:00
19/07/2018 00:00:00
A

1
30
09/12/2017 00:00:00
26/07/2018 00:00:00
P
3
2
20
02/03/2018 00:00:00
19/07/2018 00:00:00
20/07/2018 00:00:00
null
A

1
30
27/07/2018 00:00:00
27/07/2018 00:00:00
P
4
0
null
null
null
null
null
null

1
30
28/07/2018 00:00:00
31/12/9999 00:00:00
P
5
1
20
null
04/09/2018 00:00:00
05/09/2018 00:00:00
12/11/2018 00:00:00
A

1
30
28/07/2018 00:00:00
31/12/9999 00:00:00
P
5
2
20
05/09/2018 00:00:00
12/11/2018 00:00:00
13/11/2018 00:00:00
19/11/2018 00:00:00
A

1
30
28/07/2018 00:00:00
31/12/9999 00:00:00
P
5
3
20
13/11/2018 00:00:00
19/11/2018 00:00:00
20/11/2018 00:00:00
null
A


SELECT *
FROM
(
-- Principal without Actions
SELECT Employee, Position, StartDate, EndDate, PosType
FROM   #mydata
WHERE  Employee = 1
       AND P2 = 0
--
-- plus all Actions
--
UNION
SELECT Employee, P, SD, ED, PT
FROM   #mydata
WHERE  Employee = 1
       AND P2 <> 0
--
-- plus all (left) Gaps
--
UNION
SELECT Employee, 
       Position, 
       IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)), 
       DATEADD(day, -1, SD),
       PosType
FROM   #mydata
WHERE  Employee = 1
       AND P2 <> 0
       AND DATEDIFF(day, IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)), SD) > 0
UNION
--
-- plus all (last right) Gap
--
SELECT Employee, 
       Position, 
       DATEADD(day, 1, ED),
       EndDate,
       PosType
FROM   #mydata
WHERE  Employee = 1
       AND P2 <> 0
       AND NextSD IS NULL
       AND DATEDIFF(day, ED, EndDate) > 0
) T
ORDER BY 1, 3


Employee
Position
StartDate
EndDate
PosType


1
30
10/12/2016 00:00:00
11/12/2016 00:00:00
P

1
30
12/12/2016 00:00:00
30/01/2017 00:00:00
P

1
20
31/01/2017 00:00:00
02/02/2017 00:00:00
A

1
30
03/02/2017 00:00:00
28/03/2017 00:00:00
P

1
20
29/03/2017 00:00:00
31/03/2017 00:00:00
A

1
30
01/04/2017 00:00:00
18/04/2017 00:00:00
P

1
20
19/04/2017 00:00:00
21/04/2017 00:00:00
A

1
30
22/04/2017 00:00:00
21/06/2017 00:00:00
P

1
20
22/06/2017 00:00:00
23/06/2017 00:00:00
A

1
30
24/06/2017 00:00:00
28/06/2017 00:00:00
P

1
20
29/06/2017 00:00:00
30/06/2017 00:00:00
A

1
30
01/07/2017 00:00:00
02/10/2017 00:00:00
P

1
20
03/10/2017 00:00:00
06/10/2017 00:00:00
A

1
30
07/10/2017 00:00:00
08/12/2017 00:00:00
P

1
30
09/12/2017 00:00:00
11/02/2018 00:00:00
P

1
20
12/02/2018 00:00:00
02/03/2018 00:00:00
A

1
30
03/03/2018 00:00:00
18/07/2018 00:00:00
P

1
20
19/07/2018 00:00:00
20/07/2018 00:00:00
A

1
30
21/07/2018 00:00:00
26/07/2018 00:00:00
P

1
30
27/07/2018 00:00:00
27/07/2018 00:00:00
P

1
30
28/07/2018 00:00:00
03/09/2018 00:00:00
P

1
20
04/09/2018 00:00:00
05/09/2018 00:00:00
A

1
30
06/09/2018 00:00:00
11/11/2018 00:00:00
P

1
20
12/11/2018 00:00:00
13/11/2018 00:00:00
A

1
30
14/11/2018 00:00:00
18/11/2018 00:00:00
P

1
20
19/11/2018 00:00:00
20/11/2018 00:00:00
A

1
30
21/11/2018 00:00:00
31/12/9999 00:00:00
P

Employee	Position	StartDate	EndDate	PosType	Id
1	30	10/12/2016 00:00:00	11/12/2016 00:00:00	P	1
1	30	12/12/2016 00:00:00	08/12/2017 00:00:00	P	2
1	30	09/12/2017 00:00:00	26/07/2018 00:00:00	P	3
1	30	27/07/2018 00:00:00	27/07/2018 00:00:00	P	4
1	30	28/07/2018 00:00:00	31/12/9999 00:00:00	P	5

Employee	Position	StartDate	EndDate	PosType	SubId
1	20	31/01/2017 00:00:00	02/02/2017 00:00:00	A	1
1	20	29/03/2017 00:00:00	31/03/2017 00:00:00	A	2
1	20	19/04/2017 00:00:00	21/04/2017 00:00:00	A	3
1	20	22/06/2017 00:00:00	23/06/2017 00:00:00	A	4
1	20	29/06/2017 00:00:00	30/06/2017 00:00:00	A	5
1	20	03/10/2017 00:00:00	06/10/2017 00:00:00	A	6

Employee	Position	StartDate	EndDate	PosType	P1	P2	P	PrevED	SD	ED	NextSD	PT
1	30	10/12/2016 00:00:00	11/12/2016 00:00:00	P	1	0	null	null	null	null	null	null
1	30	12/12/2016 00:00:00	08/12/2017 00:00:00	P	2	1	20	null	31/01/2017 00:00:00	02/02/2017 00:00:00	29/03/2017 00:00:00	A
1	30	12/12/2016 00:00:00	08/12/2017 00:00:00	P	2	2	20	02/02/2017 00:00:00	29/03/2017 00:00:00	31/03/2017 00:00:00	19/04/2017 00:00:00	A
1	30	12/12/2016 00:00:00	08/12/2017 00:00:00	P	2	3	20	31/03/2017 00:00:00	19/04/2017 00:00:00	21/04/2017 00:00:00	22/06/2017 00:00:00	A
1	30	12/12/2016 00:00:00	08/12/2017 00:00:00	P	2	4	20	21/04/2017 00:00:00	22/06/2017 00:00:00	23/06/2017 00:00:00	29/06/2017 00:00:00	A
1	30	12/12/2016 00:00:00	08/12/2017 00:00:00	P	2	5	20	23/06/2017 00:00:00	29/06/2017 00:00:00	30/06/2017 00:00:00	03/10/2017 00:00:00	A
1	30	12/12/2016 00:00:00	08/12/2017 00:00:00	P	2	6	20	30/06/2017 00:00:00	03/10/2017 00:00:00	06/10/2017 00:00:00	null	A
1	30	09/12/2017 00:00:00	26/07/2018 00:00:00	P	3	1	20	null	12/02/2018 00:00:00	02/03/2018 00:00:00	19/07/2018 00:00:00	A
1	30	09/12/2017 00:00:00	26/07/2018 00:00:00	P	3	2	20	02/03/2018 00:00:00	19/07/2018 00:00:00	20/07/2018 00:00:00	null	A
1	30	27/07/2018 00:00:00	27/07/2018 00:00:00	P	4	0	null	null	null	null	null	null
1	30	28/07/2018 00:00:00	31/12/9999 00:00:00	P	5	1	20	null	04/09/2018 00:00:00	05/09/2018 00:00:00	12/11/2018 00:00:00	A
1	30	28/07/2018 00:00:00	31/12/9999 00:00:00	P	5	2	20	05/09/2018 00:00:00	12/11/2018 00:00:00	13/11/2018 00:00:00	19/11/2018 00:00:00	A
1	30	28/07/2018 00:00:00	31/12/9999 00:00:00	P	5	3	20	13/11/2018 00:00:00	19/11/2018 00:00:00	20/11/2018 00:00:00	null	A