I have a master file that contains unique person id, email codes, email addresses and address activity date. Four columns of information. There are various kinds of email codes, home, business, school, vendor. A person can have multiple email addresses for one email code. I want to pull the most recent email address by email type for an individual based on the email activity date.

The group by should be by unique id, email code. Then pull the max address activity date of that group of addresses. I can't seem to get this to work. Any ideas in how to go about this? This is what I have so far...

the PIDMEMALCODE is a field that concatenates the unique ID and email code together
the where clause is just limiting the results to a manageable size by specifying a couple of unique ID's and one particular email code 'HOME'

TABLE FILE GOREMAL
BY LOWEST GOREMAL.GOREMAL.GOREMAL_PIDM
BY GOREMAL.GOREMAL.GOREMAL_EMAL_CODE
BY HIGHEST GOREMAL.GOREMAL.GOREMAL_ACTIVITY_DATE
BY GOREMAL.GOREMAL.GOREMAL_EMAIL_ADDRESS
BY GOREMAL.GOREMAL.PIDMEMALCODE
WHERE ( GOREMAL.GOREMAL.GOREMAL_PIDM EQ 43175 OR 3305 OR 238805 ) AND ( GOREMAL.GOREMAL.GOREMAL_EMAL_CODE EQ 'HOME' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

This message has been edited. Last edited by: <Kathryn Henning>,

Try

BY HIGHEST 1 GOREMAL_ACTIVITY_DATE

This should retrieve the emails for the latest email activity date - hopefully just one.

Hi Trudy,

You should also look into adding this as a sub query in your GOREMAIL.MAS file.
IBI added this feature to deal with effective dating in PeopleSoft a few years ago.
Using a sub query and a passing a variable you should be able to get any point in time results.

I haven't tried it against Banner tables yet, but will in a few weeks.
Cheers
Peter

[SOLVED] the BY HIGHEST 1 worked.

Thanks everyone.