MAX date still not pulling what I want - NAITH and RSCERO help!

[mwhalen]

got it - HOWEVER - what I seem to be getting is all those in the database where the max effective date is June 4, 2002. I know there are id's in there where their max effective dates are earlier. That would say to me that I'm getting THE MOST RECENT addresses (and their id's) in the entire databse - NOT the most recent addresses for the id's. Does that make sense?

 

[dgillz]

You need to group by ID for the maximum() function to pull the maximum for that ID. Otherwise it will continue to pull the maximum for the entire dataset.

Are you using a formula to do this? If so please post it. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[rscero]

Are you trying to get the earliest or the most recent date? Your statement "where their max effective dates are earlier" indicates you want the earliest date, not the most recent. If that's the case, you need to look for the minimum date, not the maximum. Same code, just change the function being used.