Count Number of days across multiple records 3

[mkelly11]

I need to calculate the number of days between multiple records where the member ID is the same. For further clarification I'm trying to report on patients who visit an ER 3 or more times within a 30 day period. However there is a catch, a patient may visit ER once, let's say on 01/01/03 then not return until 03/01/03 and again on 03/03/03 and again on 03/05/03 and again on 03/15/03. Which if this is the scenario I do not want to count the ER visit on 01/01/03, just the following 4 visits. Any thoughts and ideas would be greatly appreciated!!

 

[PaulBricker]

So what you want to know, from your example, is a way to calculate that there were 14 days between the 3/1 visit and the 3/15 visit?

Paul

 

[mkelly11]

Actually I guess a better description would be that I need to group all records that are within a 30 day period of one another. For example:

MemberID ServiceDate
123456 01/01/03
123456 03/21/03
123456 03/24/03
123456 03/25/03
123456 04/01/03

In this scenario I would only want to report the records dated 03/21/03 through 04/01/03. If you need any further information please let me know

~Megan

 

[jromeroc]

I create an example table named Visits having the following fields (and your example data):

IdMember (Numeric)
DateVisit (Date)

1) Create the next query (named VisitsER) :

SELECT Visits.IdMember, Visits.DateVisit, [DateVisit]+30 AS Limit, (SELECT COUNT(*) FROM Visits AS NextVisits WHERE NextVisits.DateVisit >= Visits.DateVisit and NextVisits.DateVisit <= Visits.DateVisit +30 and NextVisits.IdMember=Visits.IdMember) AS VisistIn30Days
FROM Visits;

This query counts then number of visits between a period of 30 days after each dateVisit. But it's a problem: using your example data this query results in :

Id DateVisit Limit VisitsIn30Days
1 01/01/03 01/31/03 1
1 03/01/03 03/31/03 4
1 03/03/03 04/02/03 3
1 03/05/03 04/04/03 2
1 03/15/03 04/14/03 1

The result records 2 and 3 mets the criteria of 3 or more visits but are related to the same period. We must eliminate the repeated periods....

1) Create the next query (named Visits30) :

SELECT VisitsER.IdMember, VisitsER.DateVisit, VisitsER.Limit, VisitsER.VisistIn30Days FROM VisitsER
WHERE (((VisitsER.VisistIn30Days)>=3) AND (((SELECT COUNT(*) FROM VisitsER as Before WHERE Before.IdMember =VisitsER.IdMember and VisitsER.DateVisit between Before.DateVisit and Before.Limit))=1));

Now the result is :

Id DateVisit Limit VisitsIn30Days
1 03/01/03 03/31/03 4

I hope this help you...
Greetings
JRC

 

[jromeroc]

I lost the previous posts..... Using the my last query you can requery the original table to obtain the dates grouped as you wish..

 

[mkelly11]

Thank you soooooo much!!! You are the best!!!

 

[mkelly11]

I hate to do this but I have to ask a stupid question In your last post you stated that I could use your last query to requery the original table to obtain grouped dates. But your last query calls derived columns from your first query. Both your queries work great, however I do need to show all records where the service date falls within that 30 day period. Unfortunately (for me) I'm not at your skill level. Could you please clarify your last statement and/or head me in the right direction

 

[jromeroc]

Glad to be able to help. Thanks for the stars.

To obtain the detailed information using the last query, create another (yes... another) query :

SELECT Visits30.IdMember, Visits30.DateVisit AS [From], Visits30.Limit AS [To], Visits.DateVisit
FROM Visits INNER JOIN Visits30 ON Visits.IdMember = Visits30.IdMember
WHERE (([Visits].[DateVisit] Between [Visits30].[DateVisit] And [Visits30].[Limit]));

Using the same date as before, the result is :

IdMember From To DateVisit
1 03/01/03 03/31/03 03/01/03
1 03/01/03 03/31/03 03/03/03
1 03/01/03 03/31/03 03/05/03
1 03/01/03 03/31/03 03/15/03

Now you can group your report using the fields IdMember, From and To...

Test all the queries with real data, because I only used an increased version of your example data. If you need further help, let me know.

 

[mkelly11]

Your query worked GREAT!!! Thanks again for all your help