CODEWITHSUNDEEP
sqloracle-databasedatetimesql-delete
tryingToLearn
tryingToLearn

Reputation: 17

Delete all records in the table that are less than the last year's current date, but don't delete the end dates for the given months for past 2 years

Let's say I have a table that consists of just employee name and the date he/she started. I want to delete all records in the table that are less than the last year's current date, but not delete the end dates for the given months in the past two years of the current date.

For example - if the current date was '29-SEP-2020'

Emp     Date-Started
---     ------------
John    01-SEP-2020
Jane    29-SEP-2019
Adam    28-SEP-2019
Lauren  30-SEP-2019
Caleb   30-SEP-2018
Melanie 27-SEP-2018
Isaac   30-SEP-2017

The expected records to be deleted from my statement should be

Adam    28-SEP-2019
Melanie 27-SEP-2018
Isaac   30-SEP-2017

Again, please note that 30-SEP-2019 and 30-SEP-2018 won't be deleted because they are still the end dates in the range for the last two years of 2020 of September. 30-SEP-2017 records will be deleted because it is outside that range. For all of the daily dates, those records will be deleted from the last year's current date.

Upvotes: 0

Views: 2483

Answers (2)

forpas
forpas

Reputation: 164204

This will do it:

DELETE FROM tablename
WHERE DateStarted < ADD_MONTHS(sysdate, -12) 
AND DateStarted <> LAST_DAY(ADD_MONTHS(sysdate, -24))

The condition to not delete the end date of the current month of the last year is covered by the 1st condition of the WHERE clause.

See the demo.
Results (remaining rows):

> EMP    | DATESTARTED
> :----- | :----------
> John   | 01-SEP-20  
> Jane   | 29-SEP-19  
> Lauren | 30-SEP-19  
> Caleb  | 30-SEP-18

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271091

The records you want deleted correspond to:

select t.*
from t
where date_started >= add_months(sysdate, -24);

You can turn this easily into a delete statement, if you really want to delete them.

You explain a lot about end of months and stuff, but that doesn't seem relevant.

Upvotes: 0

Related Questions