Date Range Selection Formula 1

[aeljhmi]

I am having trouble putting together a date range using a table that contains information pertaining to patient appointments (past and scheduled in the future). I am trying to come up with a way to limit my results to show appointments that occurred more than twelve months ago and less than 13 months ago where there is no appointment scheduled for the next three months. For example, I run my report in December 2012 as of the end of 11/30/2012. I would like to see appointments that occurred between 11/1/2011 and 11/30/2011 where there has been no appointment from 12/1/2011 to 11/30/2012 and there is no scheduled appointment between 12/1/2012 and 2/28/2013. The goal is being able to see patients that had an appointment a year ago, never had a follow-up over the following year and have not scheduled a follow-up in the near future. I've been trying combinations using my {Table.Appt_Date} and my {Table.Appt_Status} and can't seem to put it all together. The Appt_Date refers to the date the appointment occurred or is scheduled for. Appt_Status shows the status of the appointment (Completed/Scheduled/Cancelled etc) Any thoughts would be greatly appreciated.

 

[lbass]

Use a record selection formula like this:

{table.appt_date} >= dateserial(year(currentdate)-1, month(currentdate)-1,1)

Then go to report->selection formula->GROUP and enter:

maximum({table.appt_date},{table.patient}) >= dateserial(year(currentdate)-1, month(currentdate)-1,1) and
maximum({table.appt_date},{table.patient}) < dateserial(year(currentdate)-1, month(currentdate),1)

This would return patients with no appointments since the month prior to the current month a year ago.

Be sure to use running totals if you need to count across patients, since non-groupselected records would still contribute to the more usual inserted summaries.

-LB

 

[aeljhmi]

LB,

Thank you. This works great displaying patients seen the prior month one year ago that have not had any scheduled appointments since then. The one caveat is that the business owner would like to see patients that were seen in this timeframe and do not have a scheduled appt for just the next three months only. For example, if this report is run in January 2013 looking back at December 2011, I would expect to see the following:

Patient A - First seen 12/15/11 - Next Appt 6/1/12 (Exclude as their next appt fell within the next year)
Patient B - First Seen 12/15/11 - Next Appt 2/15/13 (Exclude as their next appt falls within the next three months)
Patient C - First Seen 12/15/11 - Next Appt 5/15/13 (Include as their next appt falls ouside the next three months)
Patient D - First Seen 12/15/11 - Next Appt (none) (Include as there is no future appointment scheduled)

I may be asking too much here but is there anyway to include this "next three months only" logic in either the record or group selection to include patients that may have an appointment six months from now? Thanks for the help.

 

[lbass]

Actually my first suggestion wasn't quite right, as it could have picked up people who had no service in the target month. But note that you did also change your criteria. Anyway:

Use a record selection formula like this:

{table.appt_date} >= dateserial(year(currentdate)-1, month(currentdate)-1,1)

Then create a formula {@in15mos}:

if {table.appt_date} >= dateserial(year(currentdate)-1, month(currentdate),1) and
{table.appt_date} < dateserial(year(currentdate), month(currentdate)+3,1) then 1

Then go to report->selection formula->GROUP and enter:

{table.date} < dateserial(year(currentdate)-1, month(currentdate),1) and
sum({@in15mos},{table.patient}) = 0

-LB

 

[aeljhmi]

You're the best. Thank you. Worked as expected after I remembered to create the summary at the patient group level.