Solved

ms sql server 2005 get min date per patient, medication_type

Posted on 2009-07-01
5
791 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Nested cursor  in SQL 9 94
Export import database 4 40
SQL Server - SQL field is defined as text 3 26
My Query is not giving correct result. Please help 5 26
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

929 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

17 Experts available now in Live!

Get 1:1 Help Now