Additional column required in report - data in new column dependent on calculation on data in existing column

Post Author: dsloan

CA Forum: Formula

Hope this makes sense.

We have a Crystal Report with the following columns: -

Customer ID Service Start date of service End date of service

Report selection is set up so that Service only displays Service A, Service B, and Service C, but not Service Z, so we have the following: -

Customer ID Service Start date of service End date of service01 Service A 01/01/07 03/01/0702 Service B 13/02/07 27/02/0703 Service B 14/04/07 21/05/0704 Service C 19/03/07 23/03/07

Although Service Z is not displayed, Service Z does have have a start date and end date.

What we would like to create an additional column in the report that indicates (Y/N) if the following applies: -

Did customer have an instance of Service Z occuring before an instance of Service A, Service B, or Service C, the end date of Service Z occuring 28 days or less before the start of the instance of Service A, Service B, or Service C.

i.e.

Customer ID Service Start date of service End date of service Did Customer get service Z 28 days or less prior to A/B/C?01 Service A 01/01/07 03/01/07 Y (as customer received Service Z from 21/12/06 to 25/12/06)02 Service B 13/02/07 27/02/07 N03 Service B 14/04/07 21/05/07 N04 Service C 19/03/07 23/03/07 N

In anticipation, thank you for your help.

David and Monica