?
Solved

ms sql server 2005 get min date per patient, medication_type

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

770 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