select Max date record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select Max date record

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2010-02-10 : 19:26:01

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-10 : 20:49:35

[code]
select *
from patient p
inner join patient_supplement s on p.Acct_no = s.Acct_no
inner join
(
select Acct_no, Visit_date = max(Visit_date)
from patient
group by Acct_no
) m on p.Acct_no = m.Acct_no and p.Visit_Date = m.Visit_Date
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-11 : 07:29:45

[code]SELECT *
FROM patient p
JOIN patient_supplement s
ON p.Acct_no = s.Acct_no
WHERE NOT EXISTS
(SELECT *
FROM patient pp
WHERE p.Acct_no = pp.Acct_no
AND pp.Visit_date > p.Visit_date)

[/code]

Sachin.Nand

2937 Posts

Posted - 2010-02-13 : 02:36:25

quote:

Originally posted by ms65g

SELECT *
  FROM patient p
  JOIN patient_supplement s 
    ON p.Acct_no = s.Acct_no
 WHERE NOT EXISTS
      (SELECT *
        FROM patient pp
       WHERE p.Acct_no = pp.Acct_no
         AND pp.Visit_date > p.Visit_date)

Any reason for making the above so complicated?


SELECT pt_id,MAX(visit_date)as visitdate,(acct_no),state
 FROM Patient p
 WHERE  EXISTS
      (SELECT *
        FROM Patient_Supplemental pp
       WHERE p.Acct_no = pp.Acct_no
         )
group by pt_id,acct_no,state

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-13 : 03:01:33

Yes I want to show power of EXISTS Predicate!

quote:

Originally posted by Idera

quote:

Originally posted by ms65g

SELECT *
  FROM patient p
  JOIN patient_supplement s 
    ON p.Acct_no = s.Acct_no
 WHERE NOT EXISTS
      (SELECT *
        FROM patient pp
       WHERE p.Acct_no = pp.Acct_no
         AND pp.Visit_date > p.Visit_date)

Any reason for making the above so complicated?


SELECT pt_id,MAX(visit_date)as visitdate,(acct_no),state
 FROM Patient p
 WHERE  EXISTS
      (SELECT *
        FROM Patient_Supplemental pp
       WHERE p.Acct_no = pp.Acct_no
         )
group by pt_id,acct_no,state

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-02-13 : 03:17:13

But I guess I to have used the Exists predicate & its more simple.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources