I have a table that list a person for every month that they had insurance coverage. There is no standard beginning month and no standard ending month. There is one record for each month they were effective with a begin and end date. For example:
John Doe 1/1/03 1-31-03
John Doe 2-1-03 2-28-03
John Doe 4-1-03 4-30-03
Jane Smith 4-1-03 4-30-03
Jane Smith 5-1-03 5-31-03

I need to run a query that returns only members that have continuous dates over s specified time. i.e. Members effective for any continuous 12 month span regardless of begin or end dates.
Any help on this would be greatly appreciated.

John,

Don't know if this is the best way, but this may help:

1. Add a field called Month(EndDate)
2. Create a new query that sums the Month field per
person.
3. If field is greater than 78 (1+2+3+4...12) than the
person has been active for at least one year.
4. You can limit this by placing 78 as the criteria.

Hope this helps!


>-----Original Message-----
>I have a table that list a person for every month that
they had insurance coverage. There is no standard
beginning month and no standard ending month. There is
one record for each month they were effective with a
begin and end date. For example:
>John Doe 1/1/03 1-31-03
>John Doe 2-1-03 2-28-03
>John Doe 4-1-03 4-30-03
>Jane Smith 4-1-03 4-30-03
>Jane Smith 5-1-03 5-31-03
>
>I need to run a query that returns only members that
have continuous dates over s specified time. i.e.
Members effective for any continuous 12 month span
regardless of begin or end dates.
>Any help on this would be greatly appreciated.
>.
>

Not necessarily true if their insurance lapsed and they restarted.

--
HTH

Dale Fye


"Joe" <(E-Mail Removed)> wrote in message
news:043401c39d93$63da4c70$(E-Mail Removed)...
John,

Don't know if this is the best way, but this may help:

1. Add a field called Month(EndDate)
2. Create a new query that sums the Month field per
person.
3. If field is greater than 78 (1+2+3+4...12) than the
person has been active for at least one year.
4. You can limit this by placing 78 as the criteria.

Hope this helps!


>-----Original Message-----
>I have a table that list a person for every month that
they had insurance coverage. There is no standard
beginning month and no standard ending month. There is
one record for each month they were effective with a
begin and end date. For example:
>John Doe 1/1/03 1-31-03
>John Doe 2-1-03 2-28-03
>John Doe 4-1-03 4-30-03
>Jane Smith 4-1-03 4-30-03
>Jane Smith 5-1-03 5-31-03
>
>I need to run a query that returns only members that
have continuous dates over s specified time. i.e.
Members effective for any continuous 12 month span
regardless of begin or end dates.
>Any help on this would be greatly appreciated.
>.
>

On Tue, 28 Oct 2003 12:38:05 -0800, "Joe"
<(E-Mail Removed)> wrote:

<posting a reply to a reply, sorry!>

>I have a table that list a person for every month that
>they had insurance coverage. There is no standard
>beginning month and no standard ending month. There is
>one record for each month they were effective with a
>begin and end date. For example:
>>John Doe 1/1/03 1-31-03
>>John Doe 2-1-03 2-28-03
>>John Doe 4-1-03 4-30-03
>>Jane Smith 4-1-03 4-30-03
>>Jane Smith 5-1-03 5-31-03
>>
>>I need to run a query that returns only members that
>have continuous dates over s specified time. i.e.
>Members effective for any continuous 12 month span
>regardless of begin or end dates.

A rather snarky Subquery (which migh be REALLY slow) should work:

SELECT MemberName, StartDate
FROM yourtable
WHERE (SELECT Count(*) FROM yourtable AS X
WHERE X.MemberID = yourtable.MemberID
AND X.StartDate >= DateAdd("m", -12, Yourtable.StartDate)) = 12;


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

As a variation on John Vinson's suggestion that would also handle cases
where the begin and end date for each month aren't necessarily the first and
last day of the month, you might try something like this:

1. Create a query (say named Query1) that lists the records that mark the
beginning of a minimum period (in months) that is completely covered. The
SQL might look something like this:

PARAMETERS [Minimum Period] Long;
SELECT
[Your Table].[Member Name],
[Your Table].[Begin Date],
[Your Table].[End Date]
FROM
[Your Table]
INNER JOIN
[Your Table] AS Self
ON
[Your Table].[Member Name] = Self.[Member Name]
WHERE
Self.[Begin Date]>=[Your Table].[Begin Date]
AND
Self.[Begin Date]<DateAdd("m",[Minimum Period],[Your Table].[Begin Date])
GROUP BY
[Your Table].[Member Name],
[Your Table].[Begin Date],
[Your Table].[End Date]
HAVING
Sum([Self].[End Date]-[Self].[Begin Date]+1)
>=DateAdd("m",[Minimum Period],[Your Table].[Begin Date])-[Your
Table].[Begin Date];

This assumes the "Begin Date" and "End Date" have no time-of-day component,
and that there are no overlaps.

2. Create a second query that selects the distinct members from Query1.
The SQL might look something like this:

SELECT DISTINCT
[Query1].[Member Name]
FROM
[Query1]



"John" <(E-Mail Removed)> wrote in message
news:BA3BF132-2D97-4141-A873-(E-Mail Removed)...
> I have a table that list a person for every month that they had insurance
coverage. There is no standard beginning month and no standard ending
month. There is one record for each month they were effective with a begin
and end date. For example:
> John Doe 1/1/03 1-31-03
> John Doe 2-1-03 2-28-03
> John Doe 4-1-03 4-30-03
> Jane Smith 4-1-03 4-30-03
> Jane Smith 5-1-03 5-31-03
>
> I need to run a query that returns only members that have continuous dates
over s specified time. i.e. Members effective for any continuous 12 month
span regardless of begin or end dates.
> Any help on this would be greatly appreciated.

On second thought, I think the HAVING clause in Query1 should read:

HAVING
Sum(IIf([Self].[End Date] + 1 > DateAdd("m", [Minimum Period], [Your
Table].[Begin Date]), DateAdd("m", [Minimum Period], [Your Table].[Begin
Date]), [Self].[End Date] +1) - [Self].[Begin Date])
= DateAdd("m", [Minimum Period], [Your Table].[Begin Date]) - [Your
Table].[Begin Date];

"Brian Camire" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> As a variation on John Vinson's suggestion that would also handle cases
> where the begin and end date for each month aren't necessarily the first
and
> last day of the month, you might try something like this:
>
> 1. Create a query (say named Query1) that lists the records that mark the
> beginning of a minimum period (in months) that is completely covered. The
> SQL might look something like this:
>
> PARAMETERS [Minimum Period] Long;
> SELECT
> [Your Table].[Member Name],
> [Your Table].[Begin Date],
> [Your Table].[End Date]
> FROM
> [Your Table]
> INNER JOIN
> [Your Table] AS Self
> ON
> [Your Table].[Member Name] = Self.[Member Name]
> WHERE
> Self.[Begin Date]>=[Your Table].[Begin Date]
> AND
> Self.[Begin Date]<DateAdd("m",[Minimum Period],[Your Table].[Begin Date])
> GROUP BY
> [Your Table].[Member Name],
> [Your Table].[Begin Date],
> [Your Table].[End Date]
> HAVING
> Sum([Self].[End Date]-[Self].[Begin Date]+1)
> >=DateAdd("m",[Minimum Period],[Your Table].[Begin Date])-[Your
> Table].[Begin Date];
>
> This assumes the "Begin Date" and "End Date" have no time-of-day
component,
> and that there are no overlaps.
>
> 2. Create a second query that selects the distinct members from Query1.
> The SQL might look something like this:
>
> SELECT DISTINCT
> [Query1].[Member Name]
> FROM
> [Query1]
>
>
>
> "John" <(E-Mail Removed)> wrote in message
> news:BA3BF132-2D97-4141-A873-(E-Mail Removed)...
> > I have a table that list a person for every month that they had
insurance
> coverage. There is no standard beginning month and no standard ending
> month. There is one record for each month they were effective with a
begin
> and end date. For example:
> > John Doe 1/1/03 1-31-03
> > John Doe 2-1-03 2-28-03
> > John Doe 4-1-03 4-30-03
> > Jane Smith 4-1-03 4-30-03
> > Jane Smith 5-1-03 5-31-03
> >
> > I need to run a query that returns only members that have continuous
dates
> over s specified time. i.e. Members effective for any continuous 12 month
> span regardless of begin or end dates.
> > Any help on this would be greatly appreciated.
>
>