NOT IN subquery & dates

[rickjamesb]

I need help implementing the "NOT IN" subquery with dates. Are there any good online examples/tutorials?

For example: Lets say i had a database of customers and the date of their last order, and i only wanted to list all the customers who have not ordered in the past 2 months.

 

[JarlH]

Something like:

SELECT customername
FROM customertable
WHERE customerid NOT IN
(SELECT customerid
FROM ordertable
WHERE (current_date - orderdate) month(6)
< interval '2' month)

Note that this is ISO/ANSI compliant intervals and datetime arithmetic. Many DBMS products have their own way doing this.

 

[rickjamesb]

Thanks that helps! What does the month(6) do? And instead of "current_date" what if i had a specified date and i wanted to search 2 months in advance of that?

Would something like this work?

SELECT CustomerID, CustomerName, Phone
FROM Customers
WHERE CustomerID NOT IN
(SELECT CustomerID
FROM Orders
WHERE (04/01/2000 - OrderDate) month(6)
< interval '2' month)

I'm using Microsoft SQL Server, which probably explains why im getting errors near 'month'.

 

[JarlH]

month(6)" is used to specify that difference between the dates is to be calculated as months, with maximum precision 6.

If you are using MS SQL Server I really think the people in the MS SQL Server forum can help you with those parts.