Hello Experts,
I have a similar table where I am trying to figure out the MIN and MAX dates when a particular date is processed. Could you please help me with achieving this?
Background:
'I' represents an INSERT, 'U' represents UPDATE in this case and 'D' represents DELETE. So, when the record is first inserted and undergoes multiple changes within a day, I would like to see that record as an INSERT only for that process date. The CUR_IND is more like the latest change of that record. It could be 9999-12-31 or the day it was deactivated/deleted.
CREATE TABLE MyTable(
ID NUMBER,
CODE VARCHAR2(1),
CHG_TIME TIMESTAMP,
OPERATION VARCHAR2(1),
PROCESS_DT DATE
);
INSERT INTO MyTable VALUES (101, 'A', TO_TIMESTAMP('2018-10-22 08:56:59.000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'I', to_date('10/22/2018','mm/dd/yyyy'));
INSERT INTO MyTable VALUES (101, 'Z', TO_TIMESTAMP('2018-10-22 09:25:44.000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'U', to_date('10/22/2018','mm/dd/yyyy'));
INSERT INTO MyTable VALUES (101, 'C', TO_TIMESTAMP('2018-10-22 10:06:59.000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'U', to_date('10/22/2018','mm/dd/yyyy'));
INSERT INTO MyTable VALUES (101, 'M', TO_TIMESTAMP('2018-10-23 01:10:59.000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'U', to_date('10/23/2018','mm/dd/yyyy'));
Expected output:
SELECT ID, CODE, MIN_DT, MAX_DT, OPERATION, CUR_IND FROM MyTable WHERE PROCESS_DT = DATE'2018-10-22'
101 C 2018-10-22 9999-12-31 I Y
SELECT ID, CODE, MIN_DT, MAX_DT, OPERATION FROM MyTable WHERE PROCESS_DT = DATE'2018-10-23'
101 C 2018-10-22 2018-10-23 I N
101 M 2018-10-23 9999-12-31 U Y
Thanks!