Solved

Sql question

Posted on 2014-12-18
7
126 Views
Last Modified: 2014-12-18
Hi Experts,

I have a table containing:

ID
PatientID
DateStarted
Subject

How do I get the ID of the latest day per patient, per subject?
I would prefer to have this in one query/view.
0
Comment
Question by:bfuchs
  • 3
  • 2
  • 2
7 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 40508406
Try this:

SELECT tblYourTable.[PatientID], Last(tblYourTable.[DateStarted]) AS LastOfDateStarted, Last(tblYourTable.[Subject]) AS LastOfSubject
FROM tblYourTable
GROUP BY tblYourTable.[PatientID];

Open in new window


Flyster
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40508432
try this query


SELECT A.*
 FROM TableX AS A
 INNER JOIN
 (SELECT PatientID, Max([DateStarted]) AS MaxOfDate, Subject
      FROM TableX  GROUP BY PatientID, Subject)  AS B
      ON A.[DateStarted]=B.MaxOfDate AND A.PatientID=B.PatientID AND A.Subject=B.Subject
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40508484
@Ray,
It works great,
Just one question, if I want to exclude a subject where do I put it?

@Flyster,
I cant see how your example would work in my case, I need the ID of the record containing the highest date of patient per subject.

Thanks,
Ben
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 22

Expert Comment

by:Flyster
ID: 40508496
Sorry, missed that part. How about this:
SELECT Last(tblYourTable.ID) AS LastOfID, tblYourTable.PatientID, Last(tblYourTable.DateStarted) AS LastOfDateStarted, Last(tblYourTable.Subject) AS LastOfSubject
FROM tblYourTable
GROUP BY tblYourTable.PatientID

Open in new window


To exclude a particular subject, see the last line:
SELECT Last(tblYourTable.ID) AS LastOfID, tblYourTable.PatientID, Last(tblYourTable.DateStarted) AS LastOfDateStarted, Last(tblYourTable.Subject) AS LastOfSubject
FROM tblYourTable
GROUP BY tblYourTable.PatientID
HAVING (((Last(tblYourTable.Subject))<>"Enter_Your_Subject_Here"));

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 40508506
@Flyster,

Last(tblYourTable.ID) AS LastOfID..Group by PatientID would give me the highest ID per patient, not necessary the ID that belongs to the records containing the highest date.

Re excluding one subject, can you tell me how to modify Ray's suggestion to include this portion?

Thanks,
Ben
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40508507
change "What Subject" with the subject you want to exclude


SELECT A.*
 FROM TableX AS A
 INNER JOIN
 (SELECT PatientID, Max([DateStarted]) AS MaxOfDate, Subject
      FROM TableX  
      Where Subject <> "What Subject"
      GROUP BY PatientID, Subject)  AS B
      ON A.[DateStarted]=B.MaxOfDate AND A.PatientID=B.PatientID AND A.Subject=B.Subject
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 40508562
Excellent, Thank You!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

18 Experts available now in Live!

Get 1:1 Help Now