Date Formula

[pereda1]

Need a formula to show:

patient had surgery on 6/10/05 and left 06/11/05. Patient came back to the hospital for another surgery 28 days later. Formula to show any patient_name that has returned for a surgery within 30 days of the last surgery? Thank you

pereda1

 

[MisterMo]

could you give some details of the tables you are using
and also which database and CR version

Mo

 

[Madawc]

If you group by patient_name, you could do a summary total to get the maximum and minimum start dates (or minimum end date, depending on how you want to do it). You could then compare, Datediff("d", maxDate, minDate) < 30. Suppress the group header and details, and the group footer unless it meets the criteria.

You might also be able to suppress unwanted groups using group suppress: this depends on the version of Crystal you have.

Right-click on a field and choose Insert to get a choice of Running Total or Summary.

Right-click on a section and choose Format Section. Then choose the formula icon (x+2 and a pencil) for suppression.

The above method might fail if you have records for a long period - say someone has one operation in January and two in June. An alternative is to do the suppression on a comparison to the previous, e.g. Datediff("d", StartDate, Previous(StartDate)) >= 30. This would need the details sorted by start date.

Madawc Williams (East Anglia, UK)