Postgres 14

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

CREATE TABLE employee_table_base
(
     emp_id        INTEGER NOT NULL,
     manager_hierarchy   INTEGER NOT NULL,
     month_end_date          TIMESTAMP NOT NULL,
     assisted_flag VARCHAR(1) NOT NULL
);

INSERT INTO employee_table_base(emp_id, manager_hierarchy, month_end_date, assisted_flag)
VALUES
(12345, 1, '2021-10-31', 'N' ),
(12345, 2, '2021-10-31', 'N' ),
(12345, 3, '2021-10-31', 'N' ),
(12345, 1, '2021-11-30', 'Y' ),
(12345, 2, '2021-11-30', 'Y' ),
(12345, 3, '2021-11-30', 'Y' ),
(12345, 1, '2021-12-31', 'Y' ),
(12345, 2, '2021-12-31', 'Y' ),
(12345, 3, '2021-12-31', 'Y' ),
(12345, 1, '2022-01-31', 'Y' ),
(12345, 2, '2022-01-31', 'Y' ),
(12345, 3, '2022-01-31', 'Y' ),
(12345, 1, '2022-02-02', 'Y' ),
(12345, 2, '2022-02-02', 'Y' ),
(12345, 1, '2022-03-31', 'Y' ),
(12345, 2, '2022-03-31', 'Y' ),
(12345, 1, '2022-04-30', 'Y' ),
(12345, 2, '2022-04-30', 'Y' ),
(12345, 1, '2022-05-31', 'Y' ),
(12345, 2, '2022-05-31', 'Y' ),
(12345, 3, '2022-05-31', 'Y' ),
(12345, 1, '2022-06-30', 'N' ),
(12345, 2, '2022-06-30', 'N' ),
(12345, 1, '2022-07-31', 'Y' ),
(12345, 2, '2022-07-31', 'Y' );

CREATE TABLE



INSERT 0 25


select T.emp_id, T.manager_hierarchy, T.month_end_date, T.assisted_flag,
case 
  when (
         month_end_date = date_trunc('year', month_end_date) - interval '1 day' + interval '1 year'
         or 
         exists
              (
                 select 1 from employee_table_base D
                 where D.month_end_date = date_trunc('year', T.month_end_date) - interval '1 day' and
                       D.assisted_flag='Y'
               )
        ) 
        and assisted_flag = 'Y' 
  then 1 else 0 
end as assisted_baseline
from employee_table_base T


emp_id
manager_hierarchy
month_end_date
assisted_flag
assisted_baseline


12345
1
2021-10-31 00:00:00
N
0

12345
2
2021-10-31 00:00:00
N
0

12345
3
2021-10-31 00:00:00
N
0

12345
1
2021-11-30 00:00:00
Y
0

12345
2
2021-11-30 00:00:00
Y
0

12345
3
2021-11-30 00:00:00
Y
0

12345
1
2021-12-31 00:00:00
Y
1

12345
2
2021-12-31 00:00:00
Y
1

12345
3
2021-12-31 00:00:00
Y
1

12345
1
2022-01-31 00:00:00
Y
1

12345
2
2022-01-31 00:00:00
Y
1

12345
3
2022-01-31 00:00:00
Y
1

12345
1
2022-02-02 00:00:00
Y
1

12345
2
2022-02-02 00:00:00
Y
1

12345
1
2022-03-31 00:00:00
Y
1

12345
2
2022-03-31 00:00:00
Y
1

12345
1
2022-04-30 00:00:00
Y
1

12345
2
2022-04-30 00:00:00
Y
1

12345
1
2022-05-31 00:00:00
Y
1

12345
2
2022-05-31 00:00:00
Y
1

12345
3
2022-05-31 00:00:00
Y
1

12345
1
2022-06-30 00:00:00
N
0

12345
2
2022-06-30 00:00:00
N
0

12345
1
2022-07-31 00:00:00
Y
1

12345
2
2022-07-31 00:00:00
Y
1



SELECT 25


delete from employee_table_base;
INSERT INTO employee_table_base(emp_id, manager_hierarchy, month_end_date, assisted_flag)
VALUES
(12345, 1, '2021-11-30', 'N' ),
(12345, 2, '2021-11-30', 'N' ),
(12345, 3, '2021-11-30', 'N' ),
(12345, 1, '2021-12-31', 'N' ),
(12345, 2, '2021-12-31', 'N' ),
(12345, 3, '2021-12-31', 'N' ),
(12345, 1, '2022-01-31', 'Y' ),
(12345, 2, '2022-01-31', 'Y' ),
(12345, 3, '2022-01-31', 'Y' ),
(12345, 1, '2022-02-02', 'Y' ),
(12345, 2, '2022-02-02', 'Y' ),
(12345, 1, '2022-03-31', 'Y' ),
(12345, 2, '2022-03-31', 'Y' );

DELETE 25



INSERT 0 13


select T.emp_id, T.manager_hierarchy, T.month_end_date, T.assisted_flag,
case 
  when (
         month_end_date = date_trunc('year', month_end_date) - interval '1 day' + interval '1 year'
         or 
         exists
              (
                 select 1 from employee_table_base D
                 where D.month_end_date = date_trunc('year', T.month_end_date) - interval '1 day' and
                       D.assisted_flag='Y'
               )
        ) 
        and assisted_flag = 'Y' 
  then 1 else 0 
end as assisted_baseline
from employee_table_base T


emp_id
manager_hierarchy
month_end_date
assisted_flag
assisted_baseline


12345
1
2021-11-30 00:00:00
N
0

12345
2
2021-11-30 00:00:00
N
0

12345
3
2021-11-30 00:00:00
N
0

12345
1
2021-12-31 00:00:00
N
0

12345
2
2021-12-31 00:00:00
N
0

12345
3
2021-12-31 00:00:00
N
0

12345
1
2022-01-31 00:00:00
Y
0

12345
2
2022-01-31 00:00:00
Y
0

12345
3
2022-01-31 00:00:00
Y
0

12345
1
2022-02-02 00:00:00
Y
0

12345
2
2022-02-02 00:00:00
Y
0

12345
1
2022-03-31 00:00:00
Y
0

12345
2
2022-03-31 00:00:00
Y
0



SELECT 13

emp_id	manager_hierarchy	month_end_date	assisted_flag	assisted_baseline
12345	1	2021-10-31 00:00:00	N	0
12345	2	2021-10-31 00:00:00	N	0
12345	3	2021-10-31 00:00:00	N	0
12345	1	2021-11-30 00:00:00	Y	0
12345	2	2021-11-30 00:00:00	Y	0
12345	3	2021-11-30 00:00:00	Y	0
12345	1	2021-12-31 00:00:00	Y	1
12345	2	2021-12-31 00:00:00	Y	1
12345	3	2021-12-31 00:00:00	Y	1
12345	1	2022-01-31 00:00:00	Y	1
12345	2	2022-01-31 00:00:00	Y	1
12345	3	2022-01-31 00:00:00	Y	1
12345	1	2022-02-02 00:00:00	Y	1
12345	2	2022-02-02 00:00:00	Y	1
12345	1	2022-03-31 00:00:00	Y	1
12345	2	2022-03-31 00:00:00	Y	1
12345	1	2022-04-30 00:00:00	Y	1
12345	2	2022-04-30 00:00:00	Y	1
12345	1	2022-05-31 00:00:00	Y	1
12345	2	2022-05-31 00:00:00	Y	1
12345	3	2022-05-31 00:00:00	Y	1
12345	1	2022-06-30 00:00:00	N	0
12345	2	2022-06-30 00:00:00	N	0
12345	1	2022-07-31 00:00:00	Y	1
12345	2	2022-07-31 00:00:00	Y	1