Solved

Tracking recurring future appointments

Posted on 2010-11-19
7
693 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 63 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 62 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 6

Assisted Solution

by:ThomDroz
ThomDroz earned 62 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 63 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now