Query Customers Who Havn't Ordered in Last Six Months 1

[Melagan]

Greetings,

I have an order table I'm trying to query to give me any new orders opened by people who havn't opened any orders in the last six months; by new orders, ofcourse, I mean orders whose [Opening Date] = Date().

Schema info:
Table name = Escrows
Fields:
[Escrow Number] - (Primary Key)
[Opening Date] - Date order opened
[PDC] - Person who opened the order
[PDC_Rep] - Sales person who gets credit for the order opened.

My thoughts were to create a subquery to show all of the people who HAVE opened in the last six months then run those results against my main query, like so:

SELECT 
e.[Escrow Number]
, e.PDC
, e.PDC_Rep
, e.[Opening Date]
FROM Escrows e
WHERE
e.PDC Not In (
SELECT PDC
FROM Escrows
WHERE PDC<>"" AND [Opening Date]>Date()-180
GROUP BY PDC
)

I expect that any orders that have opened today that have not come from any specific customers from within the last six months, as defined by my subquery, would appear in this query. However, I am not getting the results I want with the above code. If this isn't enough info, I'll be happy to post some example data and expected results. Thank you!


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

One way:
SELECT e.[Escrow Number], e.PDC, e.PDC_Rep, e.[Opening Date]
FROM Escrows e INNER JOIN (
SELECT PDC, Max([Opening Date]) AS LastDate FROM Escrows
WHERE e.[Opening Date]<Date() GROUP BY PDC HAVING Max([Opening Date])<Date()-180
) AS L ON e.PDC = L.PDC
WHERE e.[Opening Date]=Date()

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

PHV,
Thank you for the prompt response, as always. I first discovered a small error in the SQL; the where in the select clause for the subquery should be [Opening Date], not e.[Opening Date]. Even so, I'm not quite getting the results I'm expecting. See below.

Example Data:

PDC PDC_Rep Escrow Number Opening Date
Marv White Ron Groove 1780281 1/29/2007
Marv White Ron Groove 1779370 2/3/2005
Marv White Ron Groove 1779329 1/10/2005
Marv White Ron Groove 524402 8/27/2004
John Baker Ron Groove 531369 3/2/2007
John Baker Ron Groove 531330 1/30/2007
Norris Chuck Ron Groove 1779848 2/10/2006
Norris Chuck Ron Groove 531307 1/10/2007
Norris Chuck Ron Groove 530598 11/16/2005
Norris Chuck Ron Groove 529829 6/14/2005
Will Johnson Ron Groove 531461 2/28/2007
Will Johnson Ron Groove 531479 4/6/2007
___
Expected Results:
PDC PDC_Rep Escrow Number Opening Date
Marv White Ron Groove 1780281 1/29/2007
John Baker Ron Groove 531330 1/30/2007
Norris Chuck Ron Groove 531307 1/10/2007
Will Johnson Ron Groove 531461 2/28/2007

___
Eventually I would like the user to be able to pick a specific [Opening Date] so they can see that if any of the orders opened on this day fit the criteria (the PDC has not opened a previous order within the last six months)



~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

So, the rules are different (I mean orders whose [Opening Date] = Date()...)
Perhaps this (typed, untested)?
SELECT e.PDC, e.PDC_Rep, e.[Escrow Number], e.[Opening Date]
FROM Escrows AS e LEFT JOIN (
SELECT PDC FROM Escrows WHERE (e.[Opening Date]-[Opening Date] Between 0 And 180
) AS L ON e.PDC = L.PDC
WHERE L.PDC Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

I'm still getting a prompt to Enter Parameter Value for e.[Opening Date] - I tried the SQL above exactly as typed but included a closing parenthesis here: (e.[Opening Date]-[Opening Date]) Between 0 And 180

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

And this (hopefully with less typo :~/) ?
SELECT e.PDC, e.PDC_Rep, e.[Escrow Number], e.[Opening Date]
FROM Escrows AS e
WHERE Not EXISTS (SELECT PDC FROM Escrows WHERE (e.[Opening Date]-[Opening Date]) Between 1 And 180)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

No typos this time
Alas, the "rules have changed" now that I've seen some example code and thought about the logic more. Baring the conversation against prompting users for info when running a query, for simplicities sake, I will be using this in my revised scenerio.

A user calls a report, whose datasource is this query we've been working on.

The user is prompted for a date. Lets call the prompt: [Enter mm/dd/yy]

This date is the date of [Opening Date] that we want to look at.

The query should return any records whose [Opening Date] is what the user asked for; and whose previous [Opening Date] is >= 180 days before the [Enter mm/dd/yy] criteria the user has entered or doesn't appear in the table previously at all.

...

I know this is a little strange but it is what the boss wants and I've had a heck of a time trying to figure it out. I think we were closest with the LEFT JOIN idea and asking for all records WHERE L.PDC Is Null. What do you think?

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Perhaps this ?
PARAMETERS [Enter mm/dd/yy] DateTime;
SELECT e.PDC, e.PDC_Rep, e.[Escrow Number], e.[Opening Date]
FROM Escrows AS e
WHERE Not EXISTS (SELECT PDC FROM Escrows WHERE (e.[Opening Date]-[Opening Date]) Between 1 And 180)
AND e.[Opening Date] = [Enter mm/dd/yy]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

I think this is going to give me what I need - based off of an earlier post.

PARAMETERS [Enter Date (mm/dd/yy format)] DateTime;
SELECT e.[Escrow Number], e.PDC, e.PDC_Rep, e.[Opening Date]
FROM Escrows AS e LEFT JOIN (
SELECT PDC, Max([Opening Date]) AS LastDate
FROM Escrows
GROUP BY PDC
HAVING Max([Opening Date])<=DateAdd("d",-180,[Enter Date (mm/dd/yy format)])
) AS L ON e.PDC = L.PDC
WHERE e.[Opening Date]=[Enter Date (mm/dd/yy format)] AND L.PDC Is Null;

Any cautions or advice for this approch?


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

I'd try this:
PARAMETERS [Enter Date (mm/dd/yy format)] DateTime;
SELECT e.[Escrow Number], e.PDC, e.PDC_Rep, e.[Opening Date]
FROM Escrows AS e LEFT JOIN (
SELECT PDC FROM Escrows WHERE ([Enter Date (mm/dd/yy format)]-[Opening Date]) Between 1 And 180
) AS L ON e.PDC = L.PDC
WHERE L.PDC Is Null AND e.[Opening Date] = [Enter Date (mm/dd/yy format)]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

And finally:

PARAMETERS [Enter Date (mm/dd/yy format)] DateTime;
SELECT [Escrow Number] & "-" & [EO Initials] AS eNum, e.PDC, e.PDC_Rep, e.[Opening Date]
FROM Escrows AS e LEFT JOIN (
SELECT PDC FROM Escrows WHERE ([Enter Date (mm/dd/yy format)]-[Opening Date]) Between 1 And 180
) AS L ON e.PDC = L.PDC
WHERE [i]e.PDC Is Not Null[/i] AND e.[Opening Date]=[Enter Date (mm/dd/yy format)] AND L.PDC Is Null;

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

You really have rows with a valid opening date and null PDC ?

 

[Melagan]

Yes, because many of the orders in the table come from internal means rather than external(PDC) parties.


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

WHERE PDC<>""

OOps, didn't notice that in your first post :~/
So, perhaps a better way:

PARAMETERS [Enter Date (mm/dd/yy format)] DateTime;
SELECT [Escrow Number] & "-" & [EO Initials] AS eNum, e.PDC, e.PDC_Rep, e.[Opening Date]
FROM Escrows AS e LEFT JOIN (
SELECT PDC FROM Escrows WHERE [!]PDC Is Not Null AND [/!]([Enter Date (mm/dd/yy format)]-[Opening Date]) Between 1 And 180
) AS L ON e.PDC = L.PDC
WHERE e.PDC Is Not Null AND e.[Opening Date]=[Enter Date (mm/dd/yy format)] AND L.PDC Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

Gotcha - works perfectly.

What is the different between these two statements?

Is Not Null
-or-
<>""

Are they not saying the same thing? Is one method preferable to the other or some reason?

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Are they not saying the same thing
No, a Null value means unknown, a "" value means ZeroLengthString.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886