CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
Kaushik
Kaushik

Reputation: 47

Set values to all subsequent rows when a condition is met at current row

Following is the base employee table. Combination of emp_id, manager_hierarchy, month_end_date is unique for the table.

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' );
emp_id manager_hierarchy month_end_date assisted_flag
12345 1 2021-10-31 00:00:00 N
12345 2 2021-10-31 00:00:00 N
12345 3 2021-10-31 00:00:00 N
12345 1 2021-11-30 00:00:00 Y
12345 2 2021-11-30 00:00:00 Y
12345 3 2021-11-30 00:00:00 Y
12345 1 2021-12-31 00:00:00 Y
12345 2 2021-12-31 00:00:00 Y
12345 3 2021-12-31 00:00:00 Y
12345 1 2022-01-31 00:00:00 Y
12345 2 2022-01-31 00:00:00 Y
12345 3 2022-01-31 00:00:00 Y
12345 1 2022-02-02 00:00:00 Y
12345 2 2022-02-02 00:00:00 Y
12345 1 2022-03-31 00:00:00 Y
12345 2 2022-03-31 00:00:00 Y
12345 1 2022-04-30 00:00:00 Y
12345 2 2022-04-30 00:00:00 Y
12345 1 2022-05-31 00:00:00 Y
12345 2 2022-05-31 00:00:00 Y
12345 3 2022-05-31 00:00:00 Y
12345 1 2022-06-30 00:00:00 N
12345 2 2022-06-30 00:00:00 N
12345 1 2022-07-31 00:00:00 Y
12345 2 2022-07-31 00:00:00 Y

Desired output:

drop table if exists employee_table_ouput;
CREATE temp TABLE employee_table_ouput
(
     emp_id        INTEGER NOT NULL,
     manager_hierarchy   INTEGER NOT NULL,
     month_end_date          TIMESTAMP NOT NULL,
     assisted_flag VARCHAR(1) NOT NULL,
     assisted_baseline INTEGER NOT NULL
);


INSERT INTO employee_table_ouput(emp_id, manager_hierarchy, month_end_date, assisted_flag, assisted_baseline)
VALUES
(12345, 1, '2021-10-31', 'N', 0 ),
(12345, 2, '2021-10-31', 'N', 0 ),
(12345, 3, '2021-10-31', 'N', 0 ),
(12345, 1, '2021-11-30', 'Y', 0 ),
(12345, 2, '2021-11-30', 'Y', 0 ),
(12345, 3, '2021-11-30', 'Y', 0 ),
(12345, 1, '2021-12-31', 'Y', 1 ),
(12345, 2, '2021-12-31', 'Y', 1 ),
(12345, 3, '2021-12-31', 'Y', 1 ),
(12345, 1, '2022-01-31', 'Y', 1 ),
(12345, 2, '2022-01-31', 'Y', 1 ),
(12345, 3, '2022-01-31', 'Y', 1 ),
(12345, 1, '2022-02-02', 'Y', 1 ),
(12345, 2, '2022-02-02', 'Y', 1 ),
(12345, 1, '2022-03-31', 'Y', 1 ),
(12345, 2, '2022-03-31', 'Y', 1 ),
(12345, 1, '2022-04-30', 'Y', 1 ),
(12345, 2, '2022-04-30', 'Y', 1 ),
(12345, 1, '2022-05-31', 'Y', 1 ),
(12345, 2, '2022-05-31', 'Y', 1 ),
(12345, 3, '2022-05-31', 'Y', 1 ),
(12345, 1, '2022-06-30', 'N', 0 ),
(12345, 2, '2022-06-30', 'N', 0 ),
(12345, 1, '2022-07-31', 'Y', 1 ),
(12345, 2, '2022-07-31', 'Y', 1 );
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

The baseline values are always calculated on the last day of the previous year

case when month_end_date = date_trunc('year', current_date) - interval '1 day') and assisted_flag = 'Y' then 1 else 0 end as assisted_baseline

Is it possible to set all the rows in the current year to 1 whenever assisted_flag = 'Y' and set as 0 whenever assisted_flag = 'N'. Looking for solution where this can accomplished without self joins.

EDIT

If an entry is present on the last day of year('2021-10-31') with assisted_flag = 'Y' only then we should include that primary key as baseline_assisted in the current.

E.g

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' );
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

Upvotes: 3

Views: 61

Answers (1)

ahmed
ahmed

Reputation: 9181

Try the following:

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

See demo.

Upvotes: 1

Related Questions