Solved

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

Posted on 2013-11-05
7
559 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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