How to extract a list of unique discharges? 1

[katrina11]

Good morning experts!

I have list of members with :
memberid
,claimid
,office Visite date
,Hospital Discharge date

memberid OfficeVisitDate HospDischDate
ss1111111 2010-08-23 2010-07-09
ss1111111 2010-09-20 2010-07-09
ss1111111 2010-11-22 2010-07-09
ss1111111 2010-12-28 2010-07-09

ss1111111 2010-12-28 2010-10-30

ss1111111 2010-12-28 2010-10-31

I need to leave unique discharges
(they are about Hospital Discharge Date).However I have multiple office Visits for the discharge
while I just need a list of unique discharges.It should look like the following:

ss1111111 2010-08-23 2010-07-09
ss1111111 2010-12-28 2010-10-30
ss1111111 2010-12-28 2010-10-31

How can I implement it in SQL code? Could you please help me with it?

Thank you for your consideration and help!

Katrin

 

[drlex]

Katrin,
In your example, you don't state the logic by which the office visit date of 2010-08-23 is chosen for the hospital discharge date of 2010-07-09. If it is merely the earliest date, try select memberid, MIN(OfficeVisitDate) , HospDischDate with grouping on memberid & HospDischDate.
You also mention claimID, but omit this from the example.


soi là, soi carré

 

[markros]

In SQL 2005 and up:

;with cte as (select *, row_number() over (partition by MemberId, HospDischDate order by OfficeVisitDate) as Rn from OfficeVisits)

select * from cte where Rn = 1 -- first visit among many for the same member and same discharge date

PluralSight Learning Library

 

[katrina11]

drlex

you don't state the logic by which the office visit date of 2010-08-23 is chosen for the hospital discharge date of 2010-07-09. If it is merely the earliest date, try select memberid, MIN(OfficeVisitDate) , HospDischDate with grouping on memberid & HospDischDate.

That is because I needed just a denominator. Thank you!