Hi Experts,
I want to delete the records grater than two weeks old.
From Saturday to Friday we are considering as Week.
P_ID CREATED_DT
105 28/AUG/2013
106 29/AUG/2013
107 30/AUG/2013
108 31/AUG/2013
109 01/SEP/2013
110 02/SEP/2013
111 03/SEP/2013
112 04/SEP/2013
113 05/SEP/2013
114 06/SEP/2013
115 07/SEP/2013
116 08/SEP/2013
117 09/SEP/2013
118 10/SEP/2013
119 12/SEP/2013
From 28/AUG/2013 to 30/AUG/2013 records has to be deleted becuase these records are two weeks old.
From 31/AUG/2013 to 06/SEP/2013 records belongs to one week and from 07/SEP/2013 to 13/SEP/2013 belongs to another week.
Today is 12/SEP/2013 so from 06/SEP/2013 to 12/SEP/2013 should be existed in the table.
Once date changed to 13/SEP/2013 then from 31/AUG/2013 to 06/SEP/2013 records has to be deleted.
I am posting table and insert scripts.
CREATE TABLE process_data(p_id NUMBER,CREATED_DT DATE);
INSERT INTO process_data VALUES(TO_DATE('28/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('29/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('30/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('31/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('01/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('02/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('03/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('04/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('05/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('06/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('07/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('08/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('09/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('10/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('11/SEP/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('12/SEP/2013','DD/MON/YYYY');
Please help me how to implement this logic.
Thanks.