select max date record

Question

select max date record

scottcabral

Default port

Points: 1495
More actions
February 10, 2010 at 5:35 pm

#71896

Hi,
i have 2 tables, Patient and Patient_Supplemental:
Patient:
Acct_no State Visit_Date PT_ID
111 RI 01/01/2010 123
222 RI 01/05/2010 123
333 RI 01/15/2010 123
Patient_Supplemental:
Acct_No State
111 RI
222 RI
I need to join both tables using Acct_No and State and get the record with the max visit_date where the records exist in both tables. In this case i should get:
PT_ID Acct_No State Visit_Date
123 222 RI 01/05/2010
Thanks
Scott

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

Use the inner join of both table with the columns on Acct_no and State, then select the max Visit_date by group the Acct_no.

Have a question, suppose your Patient table have the records like

Acct_no State Visit_Date PT_ID

111 RI 01/01/2010 123

222 RI 01/05/2010 123

222 R2 01/05/2010 123

222 R2 01/05/2010 124

333 RI 01/15/2010 123

Then which line you need to show?

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

Hi Scott,

You're new to the forum and you've just run into the same problem that a lot of folks run into. To find out what that problem is and how to fix it, take a look at the article at the first link in my signature line below.

Also, Arun doesn't understand that "RI" stands for "Rhode Island"... he thought it was R "one". But, if you substitute another state's abbreviate where he used "R2", then he asks a very good question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)