Using LAST() in Query 1

[PrgrmsAll]

I have a number of members in a table. The fields in the table are ID, LName, FName, Status, BeginDate, EndDate
Each member has a Status ('A','P','X','T') etc.
There can be multiple records per ID, however, the Begin and End dates will be different.

What I need to return from my query is two fold. Firstly I need to select the only one record per ID, that is, the record with the latest BeginDate (the last transaction). Secondly, I need to eliminate all records with a Status that is not either 'A' or 'P'.

Is it possible to do this in a single query? As part of my select, I had a Last(BeginDate) and then in the HAVING clause I included the IN ('A','P'). This does not work since it returns a record that is not the latest BeginDate for that member. (It actually seems to filter out the invalid Status' first and then it does the Last(BeginDate).)

Any help is appreciated. If it needs to be done in two separate queries then I still have a slight problem in that my Last(BeginDate) never seems to work.

 

[lupins46]

'What I need to return from my query is two fold. Firstly I need to select the only one record per ID, that is, the record with the latest BeginDate (the last transaction). Secondly, I need to eliminate all records with a Status that is not either 'A' or 'P'.'

So if the last transaction is an A or a P do you want to see it or not?

 

[PHV]

SELECT A.ID, A.LName, A.FName, A.Status, A.BeginDate, A.EndDate
FROM [your table] AS A INNER JOIN (
SELECT ID, Max(BeginDate) AS LastDate FROM [your table]
WHERE Status In ('A','P') GROUP BY ID
) AS L ON A.ID = L.ID AND A.BeginDate = L.LastDate

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

 

[PrgrmsAll]

If it is an A or a P I do want to see it, but only if that A or P record happens to be the latest transaction.

 

[PHV]

Ah, rules clarified:
SELECT A.ID, A.LName, A.FName, A.Status, A.BeginDate, A.EndDate
FROM [your table] AS A INNER JOIN (
SELECT ID, Max(BeginDate) AS LastDate FROM [your table] GROUP BY ID
) AS L ON A.ID = L.ID AND A.BeginDate = L.LastDate
WHERE Status In ('A','P')

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

 

[zoroaster]

In which case I think the

WHERE Status In('A','P')

needs to come outside the subquery?

Laters, Z

"42??? We're going to get lynched!

 

[zoroaster]

Too late, you got there 1st PHV!

Laters, Z

"42??? We're going to get lynched!

 

[PrgrmsAll]

PHV - you're a star, thanks a million. It worked like a charm with the Status filter outside.

Zoroaster and Lupins, thank you also.

Very happy. THANKS!