Solved

Tracking recurring future appointments

Posted on 2010-11-19
7
701 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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