How to compare date annually to a static date

[beacon5]

Hi everybody,

[Crystal Reports 2008]

I'm going to start with the background first and then I'll get into my question. I work at a hospital and the patients here have to have certain assessments completed annually compared to the date they were admitted to the facility. We use an electronic medical record, so every assessment that is entered has a Data Entry Date.

I need to compare the patient's admission date to the last completed assessment each year to determine if the assessment is overdue for that occurrence. So, if a patient is admitted today, I need to determine at this time next year if an annual assessment was completed. The year after that I would need to determine if an assessment was completed compared to the admission date...and so on and so forth until the end of time. Well, maybe not the end of time, but you get the idea.

I would need the current assessment to show as delinquent until it has been entered into the system (i.e. the Data Entry Date is finalized). So, once it's delinquent, I don't care about subsequent years showing as delinquent until the most recent assessment is completed and finalized.

I'm trying to include each of these assessments in an individual details section as a subreport for each patient.

I hope I've done enough to explain this thoroughly. If I can provide any other helpful information, please don't hesitate to ask.

Thanks, in advance, for any help you can provide to me and my feeble brain. =)

beacon

 

[lbass]

I'm not sure why you are planning to use subreports. You should be able to compare the difference in days between the admission date and the currentdate, divide by 365, and then compare that to the number of assessment dates. For more help, we would need to see the fields you are working with, along with some sampe data and expected results.

-LB

 

[beacon5]

Hi LB,

Thanks for the response. Here's some more info:

Each of the assessments is in its own table and there's a table for a patient's data. For instance, we have the following table structure:

Table: tblPatientData
Field: tblPatientData.PatientID
Field: tblPatientData.AdmissionDate

Assessment Type #1
Table: tblPsychiatricEvaluation
Field: tblPsychiatricEvaluation.DataEntryDate
Field: tblPsychiatricEvaluation.CompletionStatus

Assessment Type #2
Table: tblMedicalHistory
Field: tblMedicalHistory.DataEntryDate
Field: tblMedicalHistory.CompletionStatus

Assessment Type #3
Table: tblPhysicalExam
Field: tblPhysicalExam.DataEntryDate
Field: tblPhysicalExam.CompletionStatus

As you can see from the structure above, each of the assessment tables has fields that are identical to fields in other assessment tables, except that they are unique to that table (i.e. each table has a DataEntryDate, but the DataEntryDate field for tblPsychiatricEvaluation has no relation to the DataEntryDate field for the tblMedicalHistory).

Since each assessment could potentially pull hundreds/thousands of records for a patient and because I'm trying to include each assessment type on the report, I thought that subreports would be the best way to achieve this.

As for the original question about comparing the annual date, I'd like to pull the last record for each assessment type where the CompletionStatus field equals "Complete". Then, I'd like to compare the admission date to the current date to see if the DataEntryDate for the last completed record was completed at the annual mark.

So, if I have a patient that was admitted 01/01/2010, the patient should have an annual assessment for Psychiatric Evaluation, Medical History, and Physical Exam completed on or before 01/01/2011 (which I'll call the Annual Date). If the DataEntryDate for the last completed record is greater than the Annual Date, then the record would be delinquent. If it's less than the Annual Date, then the record would be within the timeframe.

However...and this is the part that I'm having a lot of trouble with...if the patient has been here more than one year, I still need to be able to compare the date of admission to the current date and the data entry date of the last completed record. So, working with the example from above, if a patient is admitted on 01/01/2010 AND the patient had assessments for Psychiatric Evaluation, Medical History, and Physical Exam completed before 01/01/2011, but didn't have assessments for Medical History completed by 01/01/2012, how would I handle this? I'm not sure how to compare the DataEntryDate to the difference of (CurrentDate - AdmissionDate)/365.

Here's the same example spelled out with just data and without the explanation:

Patient Admitted: 01/01/2011
Current Date: 03/01/2012 (306 days until 01/01/2013)
Last Psychiatric Evaluation Completed: 12/30/2011  Within Timeframe (306 days left until next annual assessment due)
Last Medical History Completed: 01/01/2011 [b]Delinquent[/b] (would say Delinquent until next assessment is completed)
Last Physical Exam Completed: 01/01/2012 Within Timeframe (306 days until next annual assessment due)

I hope this shows how I'm having to compare the static Admission date to different assessment completion dates, and how I need to be able to tell whether or not the assessment was completed within the annual window for the given year.

Thanks,
beacon

 

[lbass]

Is there a Patient ID field in each assessment table? Or what field do the Assessment tables contain that would allow you to relate the data to a specific patient? Or is there an intervening table that allows you to make this connection?

-LB

 

[beacon5]

There's a PatientID field in each assessment table and there's a PatientID field in the tblPatientData table (technically, I think the tblPatientData is the intervening table). I think the front-end inserts the PatientID into each table as new records are entered. I don't have full access to the front or the back-end, so I have to speculate on the setup. Either way, I plan on using the PatientID field from the tblPatientData table in the main report and the subreports to identify the specific patient and the related records.

I was going to use the PatientID field as the subreport link, although I wasn't going to include it as a visible field on each of the subreports.

 

[lbass]

Okay, good. Then you should link on the Patient ID and also link the admission date field to the data entry date field in each sub. Then in each sub, go into report->selection formula->record and change the selection formula to (here for the psych sub):

{tblPsychiatricEvaluation = {?pm-tblPatientData.PatientID} and
{tblPsychiatricEvaluation.DataEntryDate} > {?pm-tblPatientData.AdmissionDate}

Then in the field explorer, create a formula something like this:

if
(
isnull({tblPsychiatricEvaluation.DataEntryDate}) and
int((currentdate-{?pm-tblPatientData.AdmissionDate})/365) >= 1
) or
(
maximum({tblPsychiatricEvaluation.DataEntryDate}) > dateadd("d",365*count({tblPsychiatricEvaluation.DataEntryDate}),{?pm-tblPatientData.AdmissionDate})
) or
(
count({tblPsychiatricEvaluation.DataEntryDate}) < int((currentdate-
{?pm-tblPatientData.AdmissionDate})/365)
)
then
"Delinquent" else
"Within Timeframe"

I'm not totally clear on your definition of delinquency though.

-LB

 

[beacon5]

Delinquency, for this report, is defined as an assessment not being completed annually. The only way I can really determine if an assessment was completed annually is to see if one was completed on or before the anniversary of the patient's admission.

I will try out your suggestion. Would it be possible to explain what the code is doing? I understand parts of it, but not all of it. I'm also not exactly sure why I would link the DataEntryDate to the AdmissionDate.

 

[lbass]

If there can never be data entry dates in the assessment tables from earlier admissions, then you don't need to link on admission date.

The first clause identifies cases where there is no assessment date but over a year has elapsed from the admission date.

The second clause identifies cases where the most recent data entry date is greater than the expected deadline (which is calculated by the number of data entry dates times 365 added to the admission date).

The third clause checks to see if the number of data entry dates is less than the expected number based on the admisstion date compared to the currentdate.

-LB