Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Selecting very specific records ... Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 7:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 7:07 AM
Points: 5, Visits: 9
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
Post #1421404
Posted Tuesday, February 19, 2013 12:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
You can use ROW_NUMBER() to achieve the desired results

SELECT	*
FROM (
SELECT ROW_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
FROM Client Client
INNER JOIN Patient Patient ON Client.ID = Patient.ClientID
WHERE Patient.RptYear = 2012 AND Patient.PrimDiag <> 'NULL' AND Patient.PrimDiag <> '' AND Patient.SSN <> ''
) P
WHERE P.RN > 1




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1421452
Posted Tuesday, February 19, 2013 7:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 7:07 AM
Points: 5, Visits: 9
Thank you ... that did it!

-Mike
Post #1421639
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse