CODEWITHSUNDEEP
sqloracle-database
Abha
Abha

Reputation: 347

How to write a Query to find out if the item has been purchased in the last year

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.

**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 ?

Upvotes: 0

Views: 574

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

In general, you would have a table items with one row per item.

Then the best approach is:

select i.*
from items i
where not exists (select 1
                  from purchases p
                  where p.item = i.item and
                        p.purchase_date > add_months(sysdate, -12)
                 );

Upvotes: 0

forpas
forpas

Reputation: 164089

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)

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You can use the GROUP BY as following:

SELECT ITEM 
FROM
    (SELECT ITEM, 
            MAX(Purchase_Date) AS MX_Purchase_Date 
       FROM YOUR_TABLE
       GROUP BY ITEM)
WHERE MX_Purchase_Date < ADD_MONTHS(SYSDATE,-12)

Another option is to use NOT EXISTS as following:

SELECT DISTINCT
    ITEM
FROM YOUR_TABLE TOUT
WHERE NOT EXISTS 
    (SELECT 1 FROM YOUR_TABLE TIN
    WHERE TOUT.Purchase_Date > ADD_MONTHS(SYSDATE,-12) 
    AND TOUT.ITEM = TIN.ITEM)
AND TIN.Purchase_Date < ADD_MONTHS(SYSDATE,-12) ;

Cheers!!

Upvotes: 1

GMB
GMB

Reputation: 222462

You can use aggregation and filter with a having clause:

select item
from mytable
group by item
having max(case when purchase_date > add_months(sysdate, -12) then 1 end) is null

Upvotes: 1

Related Questions