Solved

T-SQL:  How to JOIN between tables and return the Max(date)

Posted on 2013-11-05
7
517 Views
Last Modified: 2013-11-06
Hello,
I have 2 tables patients(patid,patname) ,  Rx (rx#,patid,drug,dispenseddt) which are joined on by the patientid

A patient  can have multiple medicines dispensed in the RX table.

How to find for each patient the latest dispensedate along with other fields patid and patname

Thanks.
0
Comment
Question by:Star79
7 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39624577
SELECT p.*
FROM patients p
INNER JOIN
(
SELECT patid, MAX(dispenseddt) dispenseddt FROM Rx
GROUP BY patid
) r on p.patid = r.patid AND p.dispenseddt = r.dispenseddt
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
ID: 39624595
SELECT p.patid, p.patname, MAX(r.dispenseddt ) dispenseddt 
FROM patients p
INNER JOIN Rx r on p.patid = r.patid 
GROUP BY p.patid, p.patname

Open in new window


Also should work

Raj
0
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39624596
something like this?

Select t1.patientid , Max(dispensedt) as DispensedDate
From table1 t1
join table2 t2
On t1.patientid = t2.patientid
Group By t1.PatientId
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 32

Expert Comment

by:awking00
ID: 39624868
select patid, patname, rx#, drug, dispenseddt from
(select r.patid, p.patname, r.rx#, r.drug, r.dispenseddt,
 row_number() over (partition by r.patid order by r.dispenseddt desc) rn
 from patients p, rx r
 where p.patid = r.patid) as x
where x.rn = 1;
0
 
LVL 32

Expert Comment

by:awking00
ID: 39625279
Perhaps I misunderstood the question, but that solution won't provide the prescription number and drug values which I thought you also wanted.
0
 

Author Comment

by:Star79
ID: 39627648
awking00,
I only wanted the patid and patname along with the dispensdt
 to be retrieved
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now