Solved

ms sql server 2005 get min date per patient, medication_type

Posted on 2009-07-01
5
789 Views
Last Modified: 2012-05-07
Hi,
I have a table with the following information:

pt_num, med_code, med_name, med_type, med_date
     1           123            abc                 a          2000/01/17
      1          148            bgh                 a          2001/02/09
      1          368            ghi                   b         2001/03/09
...

and I need to get something like
pt_num      med_code , med_name, Med_type, med_date
     1               123              abc              a            2000/01/17
     1                368            ghi                   b         2001/03/09

basically, the first patient-medication of each medication_type

any suggestions? I've tried some things, but I get stuck when I group by med_code, or if I get all first medications for each patient by med code and then I try to group by med_type keeping in the med_code and all the other stuff.

Any suggestions?
Thanks!
 
0
Comment
Question by:garus
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24758828

SELECT *
FROm urTable u
WHERE med_Date = (SELECT  MIN(Med_Date) from urTable t where t.Pt_num = u.pt_num )
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24758841
Have you tried:

SELECT t1.pt_num, t1.med_code, t1.med_name, t1.med_type, t1.med_date
FROM SomeTable t1 INNER JOIN
      (SELECT t2.pt_num, t2.med_type, MIN(med_date) AS med_date
      FROM SomeTable t2
      GROUP BY t2.pt_num, t2.med_type) q ON t1.pt_num = q.pt_num AND t1.med_type = q.med_type
ORDER BY t1.pt_num, t1.med_type
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24759523
I beleive you also have to iinclude the date in the self-join.  So to plagiarize matthewspatrick solution (no points please):
SELECT  t1.pt_num,
        t1.med_code,
        t1.med_name,
        t1.med_type,
        t1.med_date
FROM    SomeTable t1
        INNER JOIN (
                  SELECT      t2.pt_num,
                              t2.med_type,
                              MIN(med_date) AS med_date
                  FROM      SomeTable t2
                  GROUP BY
                              t2.pt_num,
                              t2.med_type
                   ) q ON t1.pt_num = q.pt_num
                                          AND t1.med_type = q.med_type
                                          AND t1.med_date = q.med_date
ORDER BY
            t1.pt_num,
        t1.med_type
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24759527
acperkins said:
>>I beleive you also have to iinclude the date in the self-join.

Quite right.  All three columns are needed :)
0
 

Author Closing Comment

by:garus
ID: 31598996
Yes, just figured that out. I was getting tons of rows and then I realized there was no date.
Thank you guys!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 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

14 Experts available now in Live!

Get 1:1 Help Now