Continuous Dates

[Guest]

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.

 

[Joe]

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.

 

[Dale Fye]

Not necessarily true if their insurance lapsed and they restarted.

--
HTH

Dale Fye


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.

 

[John Vinson]

On Tue, 28 Oct 2003 12:38:05 -0800, "Joe"

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:
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;

 

[Brian Camire]

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]

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.

 

[Brian Camire]

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];

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]

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.