Selecting products that haven't been made in 2 years

Question

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.

Answer 1

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())

Answer 2

Maybe something like this?

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

Answer 3

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.

Answer 4

GROUP BY with HAVING

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