Return Dates Between Exclude Recent Dates

[DarrenBoyer]

I work for a car dealership and we are trying to query our database to return a segment of customers who are likely to want to switch out their vehicle.

The goal is to get all previous Sunfire owners who have purchased a vehicle from 99-01 but exclude the Sunfire owners who have bought since 01. This sounds simple except when you have multiple purchaser's of Sunfire's, before 01 & after 01!

I tried looking at the Distinct fcn or Unique Values in the design grid but no matter how I worked with those they didn't seem to help.

I then thought about using a nested query but still couldn't seem to be able to put it all together. All the data is properly normalized it's just that I don't know what SQL fcn might be applicable. Any suggestions?

 

[TonyJollans]

Hi DarrenBoyer,

Try this ..

[blue][tt]SELECT Customer
FROM SalesTable T1
WHERE PurchaseDate Between #1/1/1999# And #12/31/2001#
AND NOT EXISTS ( SELECT Customer
FROM SalesTable T2
WHERE T1.Customer = T2.Customer
AND T2.PurchaseDate > #12/31/2001# )[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DarrenBoyer]

Tony,

I was excited to see the solution and from what I can tell it appears you've recommended a subquery using the NOT EXISTS statement.

I've tried various options the last 2 1/2 hrs and haven't been able to get the syntax right. Usually getting the msg. - missing operator. Here is my literal translation of what you wrote:
SELECT Customers.CustomerID, [Manual-Vehicles].CustomerID, [Manual-Vehicles].UniqueID, [Manual-Vehicles].TransactionDate, [Manual-Vehicles].InventorySource, [Manual-Vehicles].ModelNumber
FROM Customers INNER JOIN [Manual-Vehicles] ON Customers.CustomerID = [Manual-Vehicles].CustomerID
WHERE ((([Manual-Vehicles].TransactionDate) Between #4/1/1999# And #4/30/2001#) AND (([Manual-Vehicles].InventorySource)="New") AND (([Manual-Vehicles].ModelNumber)="2JB37" Or ([Manual-Vehicles].ModelNumber)="2JB69"))
ORDER BY [Manual-Vehicles].TransactionDate AND NOT EXISTS (SELECT [Manual-Vehicles].CustomerID FROM [Manual-Vehicles] WHERE ((Customers.CustomerID = [Manual-Vehicles].CustomerID) AND ([Manual-Vehicles].TransactionDate > #5/1/01#)));

Which says I have a syntax error.

I also tried to rewrite the above to select all fields and tables in an INNER JOIN in the subquery but was told I was missing an operator in most attempts. If someone wouldn't mind posting a suggestion I would appreciate it knowing the weekend came and I got the GM his list of Sunfire's.

 

[Golom]

You have the "NOT EXISTS" as part of the ORDER BY clause rather than the WHERE clause. Try this
[blue][tt]
SELECT C.CustomerID, V.CustomerID, V.UniqueID,
V.TransactionDate, V.InventorySource, V.ModelNumber

FROM Customers C INNER JOIN [Manual-Vehicles] V
ON C.CustomerID = V.CustomerID

WHERE V.TransactionDate Between #4/1/1999# And #4/30/2001#)

AND V.InventorySource ="New"

AND V.ModelNumber IN ('2JB37', '2JB69')

AND NOT EXISTS
(SELECT M.CustomerID
FROM [Manual-Vehicles] M
WHERE M.CustomerID = C.CustomerID
AND M.TransactionDate > #5/1/01#)

ORDER BY V.TransactionDate
[/tt][/blue]

 

[DarrenBoyer]

Thank you so much.

Sorry about the messy SQL, won't happen again.

If either of you ever want a Sunfire I'll see what I can do to get you the best fleet discount I can!