# question

## How to use Recursion or Tally table on this problem

1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

·
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)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
Have you considered temp table and quirky update?

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Looks right to me! ;-)
0 Likes 0 ·