Continuous Dates

G

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.
 
J

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
Click to expand...
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
Click to expand...
have continuous dates over s specified time. i.e.
Members effective for any continuous 12 month span
regardless of begin or end dates.
 
D

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
Click to expand...
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
Click to expand...
have continuous dates over s specified time. i.e.
Members effective for any continuous 12 month span
regardless of begin or end dates.
 
J

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.
Click to expand...

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

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
Click to expand...
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 said:
I have a table that list a person for every month that they had insurance
Click to expand...
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
Click to expand...
over s specified time. i.e. Members effective for any continuous 12 month
span regardless of begin or end dates.
 
B

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

Brian Camire said:
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
Click to expand...
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 said:
I have a table that list a person for every month that they had
Click to expand...
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
Click to expand...
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cartesain Query 7
Cartesian Query 1
Date parameter 3
Help with Query (or Macro??) 3
Date lookup in Table 2
Date queries 1
Select Top Query 2
Monthly Data Analysis 2

Top