Getting monthly data with latest records in oracle

In this blog I am going to show how we can get monthly data for a given set of records. In many cases we try to get the data into reports with monthly , weekly etc. To achive this we can use N number of tools which are provided but sometime we have to write it because there is a need by other teams or external sources to make use of these records. Let us take an small example for my table which has the records something like as below :

 

From the above result set we need to pull only the yellow colored once. Why only the yellow , the query that we need to build is like to get the audits for every month. Now if the same audit is performed in two months multiple times with changed status then we need the latest record for each month.

Here are some sample tables with insert scripts:


create table xx_audit_tab
(
AUDIT_ID VARCHAR2(4000)
,AUDIT_TITLE VARCHAR2(4000)
,MON VARCHAR2(12 BYTE)
,AUDIT_STATUS VARCHAR2(1020)
,AUDIT_CLASSIFICATION VARCHAR2(4000)
,AUDIT_TYPE VARCHAR2(4000)
,INSTANCE_ID NUMBER
,CREATION_DATE DATE
,STAGE_NAME VARCHAR2(120)
,MAX_DATE DATE );

Insert statement for sample data :

begin
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0081','Test Audit#1','SEP','Audit Started',null,null,10852947,to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0081','Test Audit#1','SEP','Audit Started',null,null,10852947,to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0081','Test Audit#1','SEP','Audit Started',null,null,10852947,to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0082','Test_0921','SEP','Audit Started',null,null,10890405,to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0082','Test_0921','SEP','Audit Started',null,null,10890405,to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0082','Test_0921','SEP','Audit Started',null,null,10890405,to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158734,to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158734,to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158734,to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158877,to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158877,to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158877,to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11179201,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178808,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178808,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11179201,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178658,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11179201,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178658,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178808,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Cancelled',null,null,11445557,to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Cancelled',null,null,11445557,to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Cancelled',null,null,11445557,to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Fieldwork',null,null,11186517,to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178658,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Fieldwork',null,null,11186517,to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Fieldwork',null,null,11186517,to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Re-opened',null,null,14396114,to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Re-opened',null,null,14396114,to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Re-opened',null,null,14396114,to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Cancelled',null,null,15273698,to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Cancelled',null,null,15273698,to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Cancelled',null,null,15273698,to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179664,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179552,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179664,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179664,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179552,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11413653,to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11413653,to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11413653,to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Final Report',null,null,11414199,to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Final Report',null,null,11414199,to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Final Report',null,null,11414199,to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415951,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415951,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415951,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415952,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415952,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415952,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179552,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
commit;
end;

 

To get the desired result we need to use “ROW_NUMBER” an analytical function .

SQL code:


select * from (
SELECT *
FROM (SELECT t.*,
ROW_NUMBER() OVER(PARTITION BY mon,audit_id ORDER BY creation_date DESC) RN
FROM xx_audit_tab t
)sub
WHERE RN = 1 )
order by audit_id
;

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s