NOT IN subquery within a range of dates

[rickjamesb]

Hello,
I just started sql programming. I have a customers table and an orders table. Basically I want the user to input a date, and from this date i want to list all the customers that have not ordered within the past 2 months of that date.

Im unfamiliar with the "NOT IN" subquery, does this layout look okay?

SELECT *
FROM Customers
WHERE CustomerID NOT IN
(SELECT CustomerID
FROM Orders
WHERE (04/01/1996 - OrderDate) month(6)
< interval '2' month)

I'm getting errors near the end of the code. I'm not sure what to do with the last 2 lines of code.

Instead of using the date 04/01/1996, how could i let the user input a date - can i replace that date with a parameter name @UserInputDate?

The code above, once fixed, will only show all the customer details from the customer table. How could i also display the last OrderDate from the Orders table?

Thanks,
Rick

 

[gmmastros]

Try this...

Select * 
From   Customers
WHERE  DateDiff(Month, '04/01/1996', OrderDate) > 2

it should return the customers that have not ordered in the last 2 months prior to '04/01/1996'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

George,

what if they have several orders?
and one is outside of the range and one is inside

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[rickjamesb]

Hello George,
That wouldnt work because the OrderDate attribute is in the Orders table. So i changed your code "From Customers" to "From Orders" so that i could see the output, and it was still wrong because it was listing several customers multiple times.

So i kept my old code and replaced my:
WHERE (04/01/1996 - OrderDate) month(6) < interval '2' month)

With your:
WHERE DateDiff(Month, '04/01/1996', OrderDate) > 2)

And i believe that works! So thank you very much...

Now i just need to find out how to let the user input a date, and how to join the orders table so i can display the orderdate with all the customer details.

 

[gmmastros]

Rick,

I see that now, and I apologize if I've confused you. I suspect the reason you got multiple customers when you changed it to the orders table is because those customers do, in fact, have multiple orders.

So, the question becomes... what order do you want to see? All of the orders outside of the date range, the most recent order, etc....



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rickjamesb]

I want to display the customers and all their details that have not ordered within a 2 month period of a user defined date (code below will just use '04/01/1998' as the date). I also want to display the date of their last order.

I'm not sure if this code is correct, but it looks good. So far it just shows all the customer details, i still need to find out how to join the Orders table so i can also display the date of there last orders:

SELECT *
FROM Customers
WHERE CustomerID NOT IN
(SELECT CustomerID
FROM Orders
WHERE DateDiff(Month, '04/01/1998', OrderDate) > 2)