Is it possible to get Max of Date field w/other info?

[glthornton]

Hi, I'm working on a SQL 2000 server and I'm trying to write a query that will return the latest instance of a patient's medication allergies. But my current query is only designed to pull all instances. Here is what my current SQL code looks like:

SELECT DISTINCT ckt.name, prov.displayname, ckd.created
FROM ((Checkdata ckd INNER JOIN Checkmark ckm ON ckd.CID = ckm.CID) INNER JOIN checktext ckt ON ckd.DataID = ckt.DataID) INNER JOIN Providers prov ON ckd.DRID = prov.DRID 
WHERE (ckt.name Is Not Null) AND (ckm.Name Like '%medication allergies%') AND (ckd.PatID= '35000')

The results back look like this:

Biaxin     Nurse #1        2008-02-13 09:00:34.783
Biaxin     Nurse #2        2007-10-05 12:38.47.860
Biaxin     Nurse #2        2006-10-26 13:05.05.297
Advil      Nurse #2        2008-01-10 11:12:15.820
Advil      Nurse #1        2007-10-05 12:30.21.031

As you can see I have 2 drugs that are documented more than 1 time. I'd like to see if a query can be written to just provide me with the latest instance for each drug. So I would like to see the results as:

Biaxin     Nurse #1        2008-02-13 09:00:34.783
Advil      Nurse #2        2008-01-10 11:12:15.820

Is there a simplistic way of accomplishing this query? Any and all assistance would greatly be appreciated.

Thank you,
Glenn

 

[bborissov]

SELECT DISTINCT ckt.name,
                prov.displayname,
                ckd.created
FROM Checkdata ckd
     INNER JOIN Checkmark ckm  ON ckd.CID    = ckm.CID
     INNER JOIN checktext ckt  ON ckd.DataID = ckt.DataID
     INNER JOIN Providers prov ON ckd.DRID   = prov.DRID
     INNER JOIN (SELECT DataID, MAX(created) AS created
                        FROM Checkdata
                 GROUP BY DataID) Test 
           ON Ckt.DataId  = Test.DataId AND
              Ckt.created = Test.created
WHERE (ckt.name Is Not Null)                   AND
      (ckm.Name Like '%medication allergies%') AND
      (ckd.PatID= '35000')

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[glthornton]

Thank you for your reply Borislav. I have run the query you have provided and I'm still not getting the results I am looking for. Although, your query did remove some of the result rows. I still have multiple duplicates. I have placed both queries in the analyzer for everyone to see. You can download the snapshot at:

http://www.driveway.com/c1g4i9y2x6

So please take a look and let me know what you think?

Kind regards,
Glenn

 

[bborissov]

Then you have multiple DataId for "Biaxin" otr i dididn't understand you query well.
Is DataId in checktext is unique for Product?


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[glthornton]

Borislav, if I run the below query I get exactly what I want except I'm trying to also pull in the the Provider Name (prov.displayname).

SELECT ckt.name, MAX(ckd.created)
FROM ((Checkdata ckd INNER JOIN Checkmark ckm ON ckd.CID = ckm.CID) INNER JOIN checktext ckt ON ckd.DataID = ckt.DataID) INNER JOIN Providers prov ON ckd.DRID = prov.DRID 
WHERE (ckt.name Is Not Null) AND (ckm.Name Like '%medication allergies%') AND (ckd.PatID= '35000')
GROUP BY ckt.name

Which returns:

Biaxin     2008-02-13 09:00:34.783

And the DataID is unique for that specific appointment/visit.

Glenn

 

[bborissov]

Ok, hat about this:

SELECT DISTINCT ckt.name,
                prov.displayname,
                ckd.created
FROM (SELECT Checkdata.*
            FROM Checkdata
      INNER JOIN (SELECT DataID, MAX(created) AS created
                         FROM Checkdata
                  GROUP BY DataID) Test
            ON Checkdata.DataId  = Test.DataId AND
               Checkdata.Created = Test.created) Ckd
     INNER JOIN Checkmark ckm  ON ckd.CID    = ckm.CID
     INNER JOIN checktext ckt  ON ckd.DataID = ckt.DataID
     INNER JOIN Providers prov ON ckd.DRID   = prov.DRID
WHERE (ckt.name Is Not Null)                   AND
      (ckm.Name Like '%medication allergies%') AND
      (ckd.PatID= '35000')

If that didn't work please provide some example data from ALL tables and what you want as a result.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP