distinct count with criteria and max date

[TheShabz]

Hi all,

I'm trying to get this in one go but the SQL just isn't coming to me. We have a program that customers can enroll in. The table stores, among other things, account numbers, application status, and application dates. Everytime something happens in relation to an application, a new record is generated.

What I need to pull is the count of account numbers in which the most recent record of a given month has a specific application status.



What I'm going with so far:

SELECT
Count(AcctNum) As CountOfAccts
FROM
(SELECT AcctNum, Max(ApplicationDate) AS MaxAppDate
FROM tblApplications
WHERE ApplicationStat = 1
AND ApplicationDate BETWEEN #7/1/2011# AND #7/31/2011#
GROUP BY AcctNum
)

correct? incorrect? better way?

EDIT: The reason I'm not just brute forcing my way is that I'll be hitting a production mainframe (yes, a mainframe) with an apprx 20 min query. DBAs aren't too thrilled with the idea anyway. I'll be skinned alive if they find me testing out my SQL =P

EDIT2: Added GROUP BY to nested query

[June7]

I think you need GROUP BY clause in the inner nested query.

[TheShabz]

Correct, I do. I had it here but missed it during transcription. Edited OP

[goodguy]

Don't you also need an alias for the inner query?