How do I iterate thru a table, 1 rec at a time?

[roswald]

I have a member master file that has a many recs per member showing start and end dates of when that person was an active member historically.
What I need to do is figure out which members were continuously enrolled for a period of time (say 1yr from today). So if today was dec 31,2002 and we were looking at recs for member number 200.....
Start End
rec1 6/1/02 null
rec2 3/1/02 5/31/02
rec3 1/1/02 2/28/02
Member number 200 was indeed continuously enrolled for 1yr from today(12/31/02).
Can anyone give me a source code example of how to accomplish this via Transact SQL using any methodology?

I am really stuck here although I am certain it can be done in sql.

Thanks alot for your help, I really appreciate it!

Bob

 

[NeilTrain]

a better set of example data would be better to work with here.

But to answer your subject line, you can make a SQL Cursor (saved me recently) to iterate through a record at a time.

use the datediff function in T-SQL to find the length of time between 2 dates.

you could add a 3rd field to your table and call it DaysActive, then do this:

Update Table Set DaysActive = DateDiff(dd,Start,End)

and that would populate the DaysActive field for each record.

Then do something like this for each memberID:

Select Sum(DaysActive) From Table Where MemberID = /*member id to lookup*/

that will give you the total days active for that member.

 

[setsuna]

A cursor is the only way I know to do a row by row, but they are very time consuming. Especially with large data.

You may want to break your process down into smaller steps.

First you could create an intermediary or temporary table that has only members which are currently active:

example code:

select member_id,start_date,end_date
into #temp_member_table
from members as a join
(select member_id,start_date,end_date
from members
where end_date is null
group by member_id ) as t1
on a. member_id=t1.member_id


this will give you the records for all the members that are currently active.

Then you could do a self join:

select a.member_id
from #temp_member_table as a join #temp_member_table as b
on a. member_id=b.member_id
where a. start_date<>b.start_date
and datediff(a.start_date,b.start_dt)<=1 and
b.end_Date is not null

I think this would work in the scenario you have above (member has 3 rows) if most members have more than three rows you may have to do a little tweaking, I hope this helps give you some ideas.

 

[VogonPoet]

Roswald,

Are you working on HEDIS? If so, I have some code for HEDIS continuous enrollment.

If not, is it healthcare data?

Michael
michaelhodes@managedcare.com

 

[roswald]

VogonPoet,
Yes this is for HEDIS, any code you could share would be great!

Bob

 

[Veejc]

If anyone has this code - would you mind sharing it? I emailed Michael who said he had it back in 2003, but the email address was undeliverable.

Thanks in advance for any help!