How to Select Patients whose First Visit fell within a date range?

[jaybar48]

Hi

I have a report with two tables. A client demographic table which contains a unique Patient ID and a visits table which contains the visit date.

The data is grouped by patient ID. I can use a running total or formula to display the earliest visit for each patient.

1) How can I select only those patients whose earliest visit
falls within a specified date range?

2) How can I then further group these patients by the year of their earliest visit?

Thanks,

Jay

 

[lbass]

Please identify your CR version as it affects which solutions are available.

-LB

 

[jaybar48]

LB

Thanks so much.

I am using CR XI

Jay

 

[lbass]

You could use a SQL expression {%mindate} to return the earliest date:

(Select min(`date`) from Visits A
where A.`patientID` = Visits.`patientID`)

The punctuation will be specific to your datasource. Check in the database->show SQL query of this or a similar report using the same database/connectivity and see what the punctuation is like there. In the record selection formula, use a formula like:

{%mindate} in {?daterange}

Then you can group on {%mindate} on change of year.

-LB

 

[jaybar48]

LB

I know NOTHING about SQL statements and don't understand SQL.

Can this be done in any other way?

Will CR 2008 (the new version) offer any other options?

Jay

 

[dgillz]

In the group selection formula:

minimum({VisitDate},{PatientID}) in {?YourDateRange}

Then create another formula: year({Visitdate}) and group ny it.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

You don't really have to understand SQL to accomplish this. If you want additional help with this method, please go to database->show SQL query and copy the current SQL query into this thread, and I'll try to assist you with the correct SQL expression.

The only other approach you could use, would be to create a fake group. You would first group by patient ID, and then create a formula {@year}:

year({visit.date})

Do as Don suggested and go to report->selection formula->GROUP and enter:

minimum({Visit.Date},{Patient.PatientID}) in {?YourDateRange}

Then insert a minimum on {@year}. Then go to report->group sort and select "minimum of {@year}". This will order your patients in order of their first year. Although you are not actually grouping, you create a fake group header by inserting an additional patient ID group header section and then dragging it so it becomes GH1_a and adding the following formula to it:

whileprintingrecords;
numbervar prevgrpname;
numbervar grpname;
prevgrpname := grpname;
grpname := minimum({@year},{Patient.patientID});

Then go to the section expert->GH1_a->suppress and enter:

whileprintingrecords;
numbervar grpname;
numbervar prevgrpname;
not onfirstrecord and
grpname = prevgrpname

-LB