Patients that return within 30 days of last visit 1

[Trogg]

Good day to all,

I am using Crystal 2011 with SQL database. I have historical visit data on patients. I want to pull patients that have a patient type of "ONC" if they came to the ER (patient type "ED") within 30 days following their "ONC" visit. I was thinking I might have to do a main and a sub... pulling the ONC patients in the main and somehow use the sub to filter them if they presented in 30 days to the ER. Stumped at the moment and would like your help with a solution. Thanks in advance!

 

[lbass]

First limit the patient type in the record selection formula to the two types and add a date for the start of the evaluation period. Then create two formulas:

//{@ONC Date}:
if {table.patienttype}="ONC" then
{table.visitdate}

//{@ER Date}:
if {table.patienttype}="ER" then
{table.visitdate}

Insert a group on patient and then go to report->selection formula->GROUP and enter:

distinctcount({table.patienttype})=2 and
{@ER Date} >{@ONC Date} and
datediff("d",{@ERDate},{@ONC Date})<= 30

-LB

 

[lbass]

Sorry, that should have been:

distinctcount({table.patienttype},{table.patientID})=2 and
{@ER Date} >{@ONC Date} and
datediff("d",minimum({@ERDate},{table.PatientID}),maximum({@ONC Date},{table.patientID}))<= 30

Not sure how this will play out if patients have more than one of each date. Would have to see some sample data to decide how to handle this.

-LB

 

[Trogg]

Thanks lbass for your quick response... Yes there can be more than one of each date for both ONC (CCCCS) and ER visits. I have included some sample data below... apologize for the formatting but not sure how to make it display right on the forum here. For privacy sake there are no names. Med rec no is the patient id. I used your date formulas to pull some records. Here is the code:

If {TSM180_MST_COD_Pat_Type.cod_dtl_ext_id}='CCCCS' then
{TPM300_PAT_VISIT.dschrg_ts}

ERDate is the same with the exception of changing it to 'ER'. I applied the group criteria but don't think it is working as it should... may be due to multiple date issues. Hope this helps.

med_rec code ONCDate ERDate
00200016 ER 09/27/2017
00200108 ER 09/04/2017
00200108 ER 09/12/2017
00200108 ER 09/14/2017
00200108 CCCCS 08/17/2017
00200108 ER 08/26/2017
00200108 ER 09/02/2017
00200108 ER 09/03/2017
00202109 ER 09/29/2017
00202225 ER 09/28/2017
00202336 ER 08/27/2017
00202360 ER 08/04/2017
00202372 ER 09/03/2017
00202386 CCCCS 08/31/2017
00202449 ER 09/05/2017
00202614 ER 08/10/2017
00202670 ER 08/31/2017
00202694 ER 09/13/2017
00202704 CCCCS 09/27/2017
00202704 CCCCS 08/14/2017
00202784 ER 08/13/2017
00202844 ER 08/19/2017
00202918 ER 08/18/2017
00202925 ER 08/29/2017
00202925 ER 09/27/2017
00202966 ER 09/07/2017
00202966 ER 09/23/2017
00203055 CCCCS 09/15/2017
00203055 CCCCS 08/18/2017
00203099 ER 09/30/2017
00203111 ER 08/06/2017
00203111 CCCCS 09/12/2017
00203111 CCCCS 08/14/2017
00203152 ER 09/16/2017
00203200 ER 08/08/2017
00203200 ER 09/06/2017

 

[lbass]

It looks to me like your sample data only has one patient that meets your criteria. If you applied my group selection formula, you should see 0020108. But on second thought, I think the last line of my formula should be changed to remove the minimum({@ERDate},{table.patientID}) and replace it just with {@ERdate}. Leave the rest of the datediff clause as is.

Please show the results you get for your sample data after implementing this change so I can see how it is displaying. This should show any ER activity within the thirty days following the most recent ONC date for that patient.

-LB

 

[Trogg]

Thanks again for your assistance...
Here is my current record selection:

{TSM180_MST_COD_Pat_Type.cod_dtl_ext_id}in['CCCCS','ER'] and//Cancer Center Patients, ER Patients
{TPM300_PAT_VISIT.dschrg_ts}in{?StartDate}to{?StopDate}

Here is the group selection (grouped on Medrec #:

distinctcount({TSM180_MST_COD_Pat_Type.cod_dtl_ext_id})=2 and
{@ERDate}>{@ONCDate}and
Datediff("d",{@ERDate},maximum({@ONCDate},{TPM300_PAT_VISIT.med_rec_no}))<=30

Here are a couple of observations... I don't think the results are eliminating patients that are ER visits only. I only want to see cancer patients that had an ER visit within 30 days of their "CCCCS" visit. Also when I add the group criteria above the @ONCDate disappears from the data and @ERDate is all that is there. Somehow the ER patients that are not Cancer center patients need to be eliminated if possible before the 30 day timeframe is evaluated... ??.. I think. Again... thanks for your expert assistance lbass.

 

[lbass]

Is the medical record no the patient ID or is it specific to a visit? Assuming that it IS the patient ID and that you have grouped on it, you just need to add the group clause to the first line of the group selection formula—look back at my code. Right now your formula is just checking the whole report to see if there are two types of patients, when it is meant to be checking whether there are two types per patient.

-LB

 

[Trogg]

The medical record number is unique to each patient. I have used it as the grouping field here. Patients also will have a visit number that is unique for that visit. All unique visits for a patient regardless of type or reason point to his\her medical record number... ie:

MR# M003516789 John Doe can have multiple unique visits:
Visit# 4000345891, 4000456987, 4000789553 etc. etc.

I was assuming the Datediff clause was taking care of the group select... I see now what you mean... so my group select before your distinctcount statement should be... ????= (????, {TPM300_PAT_VISIT.med_rec_no}) Sorry for being a little slow on the uptake here... Thanks for your help and patience.

 

[lbass]

Copy the formula below and then go to report->selection formula->GROUP and paste it in. This is the entire formula--don't add anything to it, please:

distinctcount({TSM180_MST_COD_Pat_Type.cod_dtl_ext_id}, {TPM300_PAT_VISIT.med_rec_no})=2 and
{@ERDate}>{@ONCDate}and
Datediff("d",{@ERDate},maximum({@ONCDate},{TPM300_PAT_VISIT.med_rec_no}))<=30

-LB

 

[Trogg]

Lbass... I believe this will do it! Thank you sir for your kind, professional, patient, instructional, and expert assistance! They will have to pick through the results carefully to get the exact records they want because of the possibility of multiple visits and accounts but this will produce the record set required I'm sure. Thanks again and if you ever decide to run for president let me know... you'll have my vote! :>)