DateDiff function in Subquery maybe?

[Melagan]

Greetings,

I'm trying to write an SQL statement to give me records where a specific field's value hasn't existed in the last 90 days.

This field is for customer names, called PDC.
Each record also has a date field to let me know when the customer placed an order.

So far, I've tried using a subquery to find all of the PDC values over the last 90 days and use a NOT IN(SQL) in my parent queries WHERE clause. Something like this:

SELECT e.PDC
FROM Escrows as e
WHERE e.PDC Not In (SELECT e.PDC FROM Escrows as e WHERE DateDiff("d", [Opening Date], Date()) <=89)
GROUP BY e.PDC;

That SQL, as it is, tries to run for several minutes but doesn't produce a recordset. Any ideas to point me in the right direction? Thanks!

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

 

[lespaul]

do you have an index on PDC in the Escrow table?

Does your subquery produce a record set when run independently?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Melagan]

The subquery, run independantly, gives me 179 records. Currently there is no index for PDC; I just added one and ran another test with the same result.

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

 

[lespaul]

how many records in the Escrow table? you also don't need the GROUP BY clause since you are not using any aggregate functions. Does it run any faster without it?

 

[Melagan]

The Escrows table contains just over 10,000 records. Having updated my SQL to do-away with the GROUP BY clause, I actually DO get a recordset, but it is VERY laggy in normal-view.

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

 

[Melagan]

Additionally, whenever I want to display other data with the query, it fails to produce a recordset as before I eliminated the GROUP BY clause.

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

 

[cmmrfrds]

Best to stay away from IN subqueries since these are usually extremely inefficient and using a NOT usually adds another degree of inefficency. No need to use datediff since "days" are the default in date arithmetic.

Create a query of the pdc's that were in the last 90 days.
SELECT e.PDC
FROM Escrows as e
WHERE [Opening Date] > Date()-89
GROUP BY e.PDC
save as PDClast90.

Left Join to this query and check for null records.
SELECT e.PDC
FROM Escrows as e
Left Join PDClast90 as f
On e.pdc = f.pdc
WHERE e.pdc is null

This could be done in one query by making the first query an in-line query.
SELECT e.PDC
FROM Escrows as e
Left Join
(SELECT e.PDC
FROM Escrows as e
WHERE [Opening Date] > Date()-89
GROUP BY e.PDC) as inView
On inView.pdc = e.pdc
WHERE e.pdc is null

 

[lespaul]

you still don't need that GROUP BY in this query either:

SELECT e.PDC
FROM Escrows as e
Left Join
(SELECT e.PDC
FROM Escrows as e
WHERE [Opening Date] > Date()-89) as inView
On inView.pdc = e.pdc
WHERE e.pdc is null

 

[Melagan]

That SQL works quite quickly, but doesn't give the data that I need. I'm not looking for null values in the PDC field, I am looking for values in the PDC field that exist in the database but NOT within the last 90 days.

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

 

[Melagan]

The big question is how to use the "PDCLast90" recordset as exclusionary criteria.

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

 

[cmmrfrds]

This gives all the pdc's that were in the last 90 days.
SELECT e.PDC
FROM Escrows as e
WHERE [Opening Date] > Date()-89
GROUP BY e.PDC
save as PDClast90.

This gives all the pdc's that were not in the above group.
SELECT e.PDC
FROM Escrows as e
Left Join PDClast90 as f
On e.pdc = f.pdc
WHERE e.pdc is null '- error looking at wrong side.

The where clause should be.
WHERE f.pdc is null

 

[Melagan]

Sweet! Final SQL:

SELECT DISTINCT e.PDC
FROM Escrows AS e LEFT JOIN (
SELECT e.PDC FROM Escrows as e
WHERE [Opening Date] > Date()-179
) AS inView ON e.PDC = inView.PDC
WHERE inView.PDC Is Null AND e.PDC Is Not Null;

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