CODEWITHSUNDEEP
sqloracle-database
Catfish
Catfish

Reputation: 19294

Selecting products that haven't been made in 2 years

I'm trying to get the products that havn't been made in the last 2 years. I'm not that great with SQL but here's what i've started with and it doesn't work.

Lets say for this example that my schema looks like this

prod_id, date_created, num_units_created.

I'll take any advice i can get. 

select id, (select date from table
            where date <= sysdate - 740) older,
           (select date from table
            where date >= sysdate - 740) newer 
from table 
where newer - older

I'm not being clear enough.

Basically i want all products that havn't been produced in the last 2 years. Whenever a product is produced, a line gets added. So if i just did sysdate <= 740, it would only give me all the products that were produced from the beginning up til 2 years ago.

I want all products that have been produced in the at least once, but not in the last 2 years.

I hope that clears it up.

Upvotes: 1

Views: 2173

Answers (4)

Gary Myers
Gary Myers

Reputation: 35401

GROUP BY with HAVING

select id, max(date)
from table
group by id
having max(date) < add_months(sysdate,-24)

Upvotes: 6

Roger Pate
Roger Pate

Reputation: 

SELECT id FROM table WHERE date + (365*2) <= sysdate;

Use SELECT id, date, other, columns ... if you need to get them at the same time.

Upvotes: 0

Andy White
Andy White

Reputation: 88385

Maybe something like this?

select id, date
from table
where date <= (sysdate - 730);

Upvotes: 0

Aaron
Aaron

Reputation: 7541

I'd use SQL's dateadd function. 

where date < dateadd(year,-2,getdate())

would be a where clause that would select records with date less than 2 years from the current date.

Hope that helps.

EDIT: If you want to go by days, use dateadd(d,-740,getdate())

Upvotes: 3

Related Questions