CREATE TABLE TEST1
(
DB_TYP VARCHAR(255),
T_NAME VARCHAR(255),
T_NO NUMBER,
T_DATE DATE,
T_COUNTRY VARCHAR(255),
T_L_REGION VARCHAR(255),
T_SUPP VARCHAR(255)
);
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('AAI','ORC',1,'12/1/2022','INDIA','APAC','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('AAC','ERP',2,'12/1/2022','USA','PAC','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('AAF','SAP',3,'12/1/2022','UK','EUR','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('ATA','ORC',4,'12/1/2022','SIN','EUR','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('AAT','ERP',5,'11/1/2022','UAE','ME','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('AAB','SAP',1,'11/1/2022','INDIA','APAC','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('AAG','UNK',2,'11/1/2022','USA','PAC','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('MMM','ORC',4,'11/1/2022','SIN','EUR','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('JHK','SAP',5,'11/1/2022','UK','EUR','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('HJK','ERP',7,'11/1/2022','NZ','AUS','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('GHH','ORC',3,'10/1/2022','INDIA','APAC','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('YTR','EIN',3,'10/1/2022','USA','PAC','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('FGT','ORC',2,'10/1/2022','UK','EUR','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('AAR','ERP',4,'10/1/2022','SIN','EUR','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('AAL','EIN',5,'10/1/2022','UAE','ME','INN');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('AAD','ORC',6,'10/1/2022','NZ','AUS','INN');
If I was able to show properly for 11/1/2022 and 10/1/2022 we have 6 enteries (UAE,INDIA,USA,SIN,UK,NZ) whereas for 12/2/2022 we have only 4 INDIA,USA,UK and SIN
Now I need to check my table Test1 and for latest month after data load in this case 12/2/2022 on T_DATE, T_COUNTRY and T_L_REGION and if no entry is found in our case NZ and UAE is missing
, we need to Insert Dummy Records
Example
Match on 3 columns T_DATE, T_COUNTRY and T_L_REGION (for Latest Month only)
and INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('NA','NA',0,'12/1/2022','NZ','AUS','NA');
INSERT INTO TEST1 (DB_TYP,T_NAME ,T_NO ,T_DATE,T_COUNTRY,T_L_REGION ,T_SUPP ) VALUES ('NA','NA',0,'12/1/2022','UAE','MD','NA');
Kindly help if CTE and MERGE can help me achieve this.