Selecting very specific records ...
-
In another thread I had a query where I needed to select only patient new admissions. The query finds the first entry per patient (per SSN actually) and ignores the rest. You can see that here: http://www.sqlservercentral.com/Forums/Topic1421364-1292-1.aspx
What I need now is to select only re-admissions. So basically excluding the FIRST entry for a patient. If a patient was entered only once then that record is not selected at all.
Here is the data:
Based off of the above data I'd expect to see ONLY line 5 and 7 .... patient with SSN of 123-12-3123 just happens to be the only duplicates in this sample but in a live database there could be many duplicates. Line 8 is the first admit so it too is excluded.
Does that make sense? ....please let me know if further clarification is need. I wouldn't consider myself an "advanced" SQL use but I'm learning fast! 😀
Thank you in advance.
-Mike
-
You can use ROW_NUMBER() to achieve the desired results
SELECT*FROM(
SELECTROW_NUMBER() OVER ( PARTITION BY Client.ID, Patient.SSN ORDER BY StartOfCare ) AS RN,
Client.ID, Client.ClientName, Patient.ClientID, Patient.SSN, Patient.StartOfCare, Patient.PrimDiag
FROMClient Client
INNER JOIN Patient Patient ON Client.ID = Patient.ClientID
WHEREPatient.RptYear = 2012 AND Patient.PrimDiag <> 'NULL' AND Patient.PrimDiag <> '' AND Patient.SSN <> ''
) P
WHEREP.RN > 1
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Thank you ... that did it!
-Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply