By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE masterTable(
Policy_ID INTEGER NOT NULL
,DOB INTEGER NOT NULL
,Gender VARCHAR(1) NOT NULL
,Occupation VARCHAR(3) NOT NULL
,CDC_Ind VARCHAR(1) NOT NULL
,Load_date DATE NOT NULL
,Act_Ind VARCHAR(1) NOT NULL
);
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (1,'290691','M','ABC','I','2019-01-01','N');
1 rows affected
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (2,'290692','M','ABC','I','2019-01-01','N');
1 rows affected
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (3,'290693','F','ABC','I','2019-01-01','N');
1 rows affected
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (4,'290694','M','ABC','I','2019-01-01','N');
1 rows affected
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (5,'290695','F','ABC','I','2019-01-01','N');
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (3,'290693','F','ABC','D','2019-02-28','N');
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (4,'290694','M','DEF','U','2019-02-28','N');
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (10,'290791','M','ABC','I','2019-02-28','N');
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (11,'290791','M','ABC','I','2019-03-01','Y');
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (4,'290694','M','DEF','D','2019-03-01','N');
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (10,'290791','M','ABC','D','2019-03-01','N');
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (5,'290695','F','ABC','D','2019-03-01','N');
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (1,'290691','M','ABC','D','2019-03-01','N');
INSERT INTO masterTable(Policy_ID,DOB,Gender,Occupation,CDC_Ind,Load_date,Act_Ind) VALUES (2,'290692','M','ABC','D','2019-03-01','N');
10 rows affected
select Policy_ID, DOB, Gender, Occupation
from (
select
t.*,
row_number() over(partition by Policy_ID order by Load_date desc) rn
from masterTable t
where Load_date < '2019-03-01'
) t
where rn = 1 and CDC_Ind <> 'D'
| Policy_ID | DOB | Gender | Occupation |
|---|---|---|---|
| 1 | 290691 | M | ABC |
| 2 | 290692 | M | ABC |
| 4 | 290694 | M | DEF |
| 5 | 290695 | F | ABC |
| 10 | 290791 | M | ABC |