0

I am attempting to pull a single record with the most recent affective date. However, I am getting two records with effective dates of 04/01/2010 and 12/01/2009. Can someone please tell me how to pull only the record with an effective date of 04/01/2010? Thanks for your help.

select 
T1.idNumber, T1.lastname, T1.firstname, T1.streetaddress, 
T1.city, T1.state, T1.zipcode, T1.DOB, T1.DOD, 
T2.mNumber, countycode, max(T2.effectiveDate) 
from 
PTable T1, 
MTable T2 
where 
T1.idNumber in ('12345') 
group by 
T1.idNumber, T1.lastname, T1.firstname, T1.streetaddress, 
T1.city, T1.state, T1.zipcode, T1.DOB, T1.DOD, 
T2.mNumber, T1.countycode
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by jbisono
1

First that is happening because you are not linking your two tables with a particular field, if that is what you want I would say SELECT TOP 1 THE REST OF THE CODE HERE.

But this is what I think you need and the FROM Clause Remove everything and add this
PTable AS T1 INNER JOIN
MTable AS T2 ON T1.idNumber = T2.mNumber

I'm assuming that idNumber and mNumber is the same ID. that should work.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.