Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Tracking recurring future appointments

Posted on 2010-11-19
7
Medium Priority
?
703 Views
Last Modified: 2012-05-10
New to database design, and looking for an elegant way to track patient follow-ups at a non-profit mental health clinic. Specifically, we need to know which patients are due for a 90-day consultation, and these can extend out indefinitely for as long as the patient is active.  It would obviously be easy if we just limited it to a certain number of periods, but theoretically patients could need 90-day follow-ups for years and years.  

So we have a patient.IntakeDate, and need to know which patient in any given month is coming up on another 90-day interval from their last one.  So, for instance, I would want to search the date range of 12/1/2010 - 12/31/2010, and know which patients' are due in that time frame.  Creating a hundred different fields of the IntakeDate + 90, + another 90, and so on seems crude and ridiculous, so I'm sure the experts out there have a better idea.

Even though the existing database is Filemaker Pro 11, I am open to switching to either SQL Server or MYSql, so anything relevant would be helpful.

Thanks in advance for your expertise!
0
Comment
Question by:michaelsenn
7 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 252 total points
ID: 34178684
It's easier if you think about it another way.  You're looking for patients whose last visit was 90 days ago. In other words between 9/1/2010 and 10/1/2010.  I don't use Filemaker. But most db's provide date functions that let you subtract 90 days from any given date.  For example in MySQL

StartDate: DATE_ADD('2010-12-01', INTERVAL -90 DAY)   // returns 9/2/2010
EndDate:  DATE_ADD('2010-12-31', INTERVAL -90 DAY)   // returns 10/2/2010

Just plug those dates (or functions) into your query.  The result would be something like

ie  
SELECT Columns
FROM    TableName
WHERE DateOfLastVisit >= '09-02-2010'
AND      DateOfLastVisit <= '10-02-2010'
...

NOTE - The exact values depend on whether you're storing dates only or dates and times. But you get the idea.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34178696
>> 9/1/2010 and 10/1/2010
>> '09-02-2010' ... '10-02-2010'

Ignore the discrepancy in dates. I didn't *actually* do the date math ;-)
0
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 248 total points
ID: 34179347
agx's idea is good, very simple, can be done in fm too; dates are easier to handle in fm than in sql, especially mssql2005 and below.
If I should add my point, I won't do a search, but use a global repetitive field to represent a calendar, then compare the current date range with the original entry date (no need to store the last visit), modulo 3 months.
basically: if mod(today - patient's entry date) / 90 = 0, then this patient should be selected.
this is the theory, this has to be adapted with months instead of days, but you see the idea.
and everyday, when you open the app, the current day shifts, and shows expected patients
why a repetitive field for current day? it allows to show the whole month effortless by only indicating the value of the first repetition, others can be worked out using something like this:
Extend(date_ref) +  Get ( CalculationRepetitionNumber ) -1
just magic to show a few weeks across the screen.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Assisted Solution

by:ThomDroz
ThomDroz earned 248 total points
ID: 34179496
michaelsenn

2 interesting approaches above, let me throw in a 3rd.

If you have a table (ConsultantDate) to store the dates of visit of patients. Then have a field in a related table called NextScheduledVisit.  This field could be an calculation field of the most recent date (use a variable to compute this)  in ConsultantDate +90.

Then you could search this field for a date range and find the patients that are due.  This would allow flexibility if you had a patient that was given a consultation late and still have the next consultation be in 90 days from the last visit.
0
 
LVL 9

Assisted Solution

by:jvaldes
jvaldes earned 252 total points
ID: 34179945
Another perspective, I would get a free copy of seedcode's calendar app from http://www.seedcode.com/ccp51/cgi-bin/cp-app.cgi?usr=51F2606694&rnd=1591508&rrc=N&affl=&cip=68.4.227.200&act=&aff=&pg=prod&ref=calendarfree10

And I would write a routine to auto-generate the appointment entries into the calendar. This would create an elegant interface and provide an easy entry in scheduling of tasks. Providing for you daily, weekly and monthly views.

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34180816
All great ideas guys!  
0
 

Author Closing Comment

by:michaelsenn
ID: 34181239
Fantastic feedback...will probably incorporate all ideas into a new app design.  Thanks!!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month10 days, 13 hours left to enroll

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question