date gaps

[Guest]

I am trying to count members that may have had a gap in enrollment of 30
days. How can I write a query to show me which members had gaps in
enrollment. Here are the fileds I am working with. (see below for
example....1 member has diff lines..."spans" of enrollment if you
will.......) I only want the query to be able to place the number of days of
the gap in a field to see if they were enrolled.

member ID effective date end date
12345678 10/1/1999 10/31/2000
12345678 11/1/2000 11/30/2000
12345678 05/1/2002 09/1/2002


With much appreciation,

 

[Guest]

Hi Buck

Use something like this

GapateDiff("d", [effective date], [end date])

Gap is the number of days between the 2 dates.

Hope this helps. You can use the criteria lines to filter this ie. >29
would give you 30 days or more, etc.

Hope this helps

 

[Guest]

Try this using two queries - first named [buckpeace-1].

[buckpeace-1] ---
SELECT [member ID], [effective date], [end date], (SELECT COUNT(*)
FROM [buckpeace] T1
WHERE T1.[member ID] = T.[member ID]
AND T1.[effective date] <= T.[effective date]) AS Rank
FROM buckpeace AS T
ORDER BY [member ID], [effective date], [end date];

Second query --
SELECT [buckpeace-1].Rank, [buckpeace-1].[member ID],
[buckpeace-1].[effective date], [buckpeace-1].[end date],
Min(DateDiff("d",[buckpeace-1].[end date],[buckpeace-1_1].[effective date]))
AS [Days end to start], Max([buckpeace-1_1].[effective date]) AS [Last
Effective Date]
FROM [buckpeace-1] INNER JOIN [buckpeace-1] AS [buckpeace-1_1] ON
[buckpeace-1].[member ID] = [buckpeace-1_1].[member ID]
WHERE ((([buckpeace-1_1].Rank)>[buckpeace-1].[Rank]))
GROUP BY [buckpeace-1].Rank, [buckpeace-1].[member ID],
[buckpeace-1].[effective date], [buckpeace-1].[end date];

 

[John Spencer]

I might try something like the following to get the list of member id's

SELECT [MemberID]
FROM [YourTable] as YT
WHERE DateDiff("d"
,(SELECT Max([EndDate])
FROM [YourTable] as Temp
WHERE Temp.[MemberID] = YT.[MemberID]
AND Temp.[EndDate] < YT.[EffectiveDate])
,[EffectiveDate]) >30