asked
How to use Recursion or Tally table on this problem
My problem here is how to identify 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
This web site runs on voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
is this what you are after ? ; with cte as ( select memberid, f_servicedate = min(servicedate), l_servciedate = max(servicedate) from example_data group by memberid ) select c.memberid, d.accountnumber, d.servicedate from cte c inner join example_data d on c.memberid = d.memberid and d.servicedate in (c.f_servicedate, c.l_servicedate)
this is incorrect. you are retrieving the min and max date here.. you are right about the min(date) as an admission date, but the max(date) isnt always its equivalent discharge date especially if a patient has multiple admissions
Have you considered temp table and quirky update?
Assuming that the accountnumber column is the grouping key, then something along these lines should work: create table #treatments (memberid int, accountnum char(5), servicedate datetime) go set dateformat mdy go insert #treatments select 1, 'ABC11', '10/24/2013' union all select 1, 'ABC12', '10/26/2013' union all select 1, 'ABC13', '10/28/2013' union all select 1, 'ABC12', '10/30/2013' union all select 1, 'ABC13', '11/2/2013' union all select 1, 'ABC11', '11/5/2013' union all select 1, 'ABC14', '11/30/2013' union all select 1, 'ABC15', '12/1/2013' union all select 1, 'ABC16', '12/3/2013' union all select 1, 'ABC17', '12/8/2013' union all select 1, 'ABC17', '12/10/2013' union all select 1, 'ABC16', '12/9/2013' union all select 1, 'ABC15', '12/12/2013' union all select 1, 'ABC14', '12/11/2013' go select memberid , accountnum , start_servicedate = min(servicedate) , end_servicedate = max(servicedate) from #treatments group by memberid , accountnum /* memberid accountnum start_servicedate end_servicedate ----------- ---------- ----------------------- ----------------------- 1 ABC11 2013-10-24 00:00:00.000 2013-11-05 00:00:00.000 1 ABC12 2013-10-26 00:00:00.000 2013-10-30 00:00:00.000 1 ABC13 2013-10-28 00:00:00.000 2013-11-02 00:00:00.000 1 ABC14 2013-11-30 00:00:00.000 2013-12-11 00:00:00.000 1 ABC15 2013-12-01 00:00:00.000 2013-12-12 00:00:00.000 1 ABC16 2013-12-03 00:00:00.000 2013-12-09 00:00:00.000 1 ABC17 2013-12-08 00:00:00.000 2013-12-10 00:00:00.000 */ BUT. I'm confused by a few things. What do you mean by "similar". Are you talking in the mathematical sense or the everyday sense? Why do only accountnumbers ABC11 and ABC14 appear in your expected results? Edit: No need for the extra complication of row_number columns, so took these out
I know this is an older post but there is no way possible for this to work in any way close to a reliable method. I'd also like to know the name of the hospital where Admission and Discharge dates aren't clearly marked in the data so that I can avoid it at all costs.
19 People are following this question.
