Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ms sql server 2005 get min date per patient, medication_type

Posted on 2009-07-01
5
Medium Priority
?
806 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
[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
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 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

604 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