If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
date gaps
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, -- Buck |
#2
|
|||
|
|||
date gaps
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 -- Wayne Manchester, England. Not an expert Enjoy whatever it is you do "buckpeace" wrote: 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, -- Buck |
#3
|
|||
|
|||
date gaps
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]; "buckpeace" wrote: 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, -- Buck |
#4
|
|||
|
|||
date gaps
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 "buckpeace" wrote in message ... 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, -- Buck |
Thread Tools | |
Display Modes | |
|
|