I have a table ,where I have fields like Item and Purchase date. There are multiple entries of item purchased on different dates. I want to only select those items that are NOT purchased in the last 12 months. For eg. as shown below, I just need the value of ‘IPad’ as it has never been purchased in last 12 months.
x
**Item*** **Purchase Date**
Laptop 22-Mar-19
Laptop 17-Nov-16
Laptop 26-Jan-15
Laptop 27-Nov-19
Ipad 26-Jan-17
Ipad 5-Sep-16
Ipad 12-Jan-15
How can I achieve it using an Oracle sql query ?
Advertisement
Answer
With NOT EXISTS
:
select distinct item
from tablename t
where not exists (
select 1 from tablename
where item = t.item and purchase_date > add_months(sysdate, -12)
)
or:
select item
from tablename
group by item
having max(purchase_date) < add_months(sysdate, -12)