Access Middle record from three records

[rute67]

Let me start be giving the problem.

We are a health company ad we have member that sign on with specific effective date and we are having a problem getting information for these members.

Following are three difference issues which I have already resolved the first two but am not able to anwser the third.

1> An exsiting member disenrolles and reenrolles a month later. That is straight forward I can just get the new record.

Example:
Member Effective dates
John Doe 1/1/2003 - 1/31/2003
John Doe 3/1/2003 -

2. An exsiting member changes a benefit while being enrolled. This is a small problem becuase our system only will allow us to add a new effective date for that member and that will add a new record for him but his effective date is still his prior one. I resolved this problem with a datediff query that allows me to get that allows me to get the member with his first effective date.

Example:
Member Effective dates
John Doe 1/1/2003 - 1/31/2003
2/1/2003 -

I fixed problems 1 & 2 with this query:

effectiveDateStart =
Case when datediff (dd, Min(date_to), max(date_from)) = '1' then
cast(cast(year (min(date_from))as char(4))+
right(cast(month(min(date_from))+100 as char(3)),2)+
right(cast(day(min(date_from))+100 as char(3)),2) as char(8))
else
cast(cast(year (max(date_from)) as char(4))+
right(cast(month(max(date_from))+100 as char(3)),2)+
right(cast(day(max(date_from))+100 as char(3)),2) as char(8))

end,

That Query will give me the right effective dates for the member.

3. THIS IS MY PROBLEM: If the member something in his information again while still being enrolled. That will give me three records for him and the query above will not work becuase it is looking for the max and min effective dates.

So my question is, Is there a way to look at the difference between the the last two records for each member or can I only look at the min and the max?

Thanks for any help you guys can give.

rute67

 

[rute67]

Sorry forgot example for the last problem

Example:
Member Effective dates
John Doe 1/1/2003 - 1/31/2003
2/1/2003 - 2/28/2003
3/1/2003

I need to be able to get the 1/1/2003 effective date

Example:
Member Effective dates
John Doe 1/1/2003 - 1/31/2003
3/1/2003 - 3/1/2003
4/1/2003 -

I need to be able to get the 3/1/2003 effective date

 

[Ankor]

Here are several solutions:
1. You add a timestamp field to the table and check for the last date when the record for the certain member was updated.
2. Separate the effective dates in the table, make it "Start Date" and "End Date". If you add a field with the change codes, you will be able to determine every single move of your members.
3. Add just a field with the change codes (for example: I - initial enrollment, C - information change, R - reenrollment and so on). In this case you will be able to look at only those records that are needed in the particular query.

 

[rute67]

That was one way that We had thought of but we were trying to see if there was a different way of doing it. We didn't want to have change mulitiple records for 4000 members.

But if that is the only way then I guess we have no choice.

Thanks for all the help.