• Can I use these method to resolve the problem below? Please help

    My problem here is how to identity and select a patients service date. A service date could be admission date(hospital), discharge date(hospital), CBC taken date, CBC result date, xray taken date, xray result read date etc.. . Each date is paired and are reconciled thru a similar account number. lets say an admission date is always paired with discharge date and so both dates will have similar account number, so is CBC taken date paired with CBC result date. This might happen in the same day but the time will be different. However we are receiving the data in a format where we don't know what kind of service was performed, we only have the service date. so we are assuming that a patients earliest date is an admission date and whatever date that has a similar account number is the discharge date. I want to select all patients record that contains admission date and discharge date base on the assumption that the earliest date is an admission date and the other date on that accountnumber is the discharge date. whatever dates that fall in between the admission date and discharge date will be ignored. However a patient could be admitted and discharge from the hospital multiple times a year. so the earliest date after the first discharge date will also be considered as a 2nd admission date.. so on and so forth the cycle continues..

    example data..

    memberid, accountnum, servicedate

    1 , ABC11, 10/24/2013

    1 , ABC12, 10/26/2013

    1 , ABC13, 10/28/2013

    1 , ABC12, 10/30/2013

    1 , ABC13, 11/2/2013

    1 , ABC11, 11/5/2013

    1 , ABC14, 11/30/2013

    1 , ABC15, 12/1/2013

    1 , ABC16, 12/3/2013

    1 , ABC17, 12/8/2013

    1 , ABC17, 12/10/2013

    1 , ABC16, 12/9/2013

    1 , ABC15, 12/12/2013

    1 , ABC14, 12/11/2013

    in these sample data the expected data to be return is:

    memberid, accountnumber, servicedate

    1, ABC11, 10/24/2013

    1, ABC11, 11/5/2013

    1, ABC14, 11/30/2013

    1, ABC14, 12/11/2013

    please help me . is this type of logic complex and be better implemented as a SQL CLR or recursive cte or tally table.. I could only manage to recover the first admission date and discharge date for each member...

    ;with cte1 as (

    select memberid, acctnum, servicedate,

    member_earliestdate = ROW_NUMBER()over(partition by memberid order by servicedate asc)

    from patient_admitlog)

    select * from

    (select * from cte1 where member_earliestdate = 1)t1 join

    cte1 on t1.acctnum = cte1. acctnum and t1.memberid = cte1.memberid