create table stable(sid number, column1 varchar2(20), column_track varchar2(1), screate date);
create table stablelog(sid number,column1 varchar2(20), column_track varchar2(1),schangedate date);
insert into stable values(100, 'dontcare','Y', to_date('02-OCT-2020','DD-MON-YYYY'));
insert into stable values(200,'dontcare','Y', to_date('10-DEC-2020','DD-MON-YYYY'));
insert into stable values(300,'dontcare','N', to_date('14-JAN-2021','DD-MON-YYYY'));
insert into stable values(400, 'dontcare','Y',to_date('01-FEB-2021'));
INSERT INTO stablelog values (100,'dontcare', 'N',to_date('03-JAN-2021','DD-MON-YYYY') );
INSERT INTO stablelog values (100,'dontcare', 'Y',to_date('04-JAN-2021','DD-MON-YYYY') );
INSERT INTO stablelog values (200,'dontcare', 'N',to_date('06-DEC-2020','DD-MON-YYYY') );
INSERT INTO stablelog values (200,'dontcare', 'Y',to_date('06-JAN-2021','DD-MON-YYYY') );
INSERT INTO stablelog values (200,'dontcare', 'Y',to_date('16-JAN-2021','DD-MON-YYYY') );
stablelog is log table for stable, so whatever changes in stable goes into stablelog
Now I have to find out all the records that got created after 1st Jan 2021 (screate from stable) , or in the stablelog table if the column_track was N from 1st Jan 2021 but then was changed to Y in January 2021 itself
SO SID 200 should not be in the result output because though in the log table column_track shows Y after 1st Jan 2021, it was no entry of column_track being N in Jan 2021
so basically, show all the rows that had the column_track to be N first, and then changed to Y in 2021 along with the records from stable that were created in 2021 (screate column tells you that)
Now 100 should appear because in the stablelog, as of 3-JAN-2021 it was N, but then was changed to Y on 4th jan
How do we track changes to a column in the sql ? I used below but not working, we should not see 200, and 100 is appearing only because the column_track = 'Y' in the subquery
select * from stable c
where
TRUNC (c.screate) > =
TO_DATE('01-JAN-2021','DD-MON-YYYY')
OR EXISTS
(SELECT 1
FROM stablelog l
WHERE l.sid = c.sid
AND l.schangedate >=
TO_DATE('01-JAN-2021','DD-MON-YYYY') AND L.column_track = 'Y')