Add months to date help needed!!! 3

[tektribe]

I am writing a report pulling customers who need to complete 3 month service surveys. I need to find out which customers have a 3 month survey coming up. For example, if I input the month of June, the report should pull all customers who received a service in March, regardless of the year. Or if I input the month of March, the report should pull all customers who received a service in December.

The date of service is equal to the last date that the customer came in a for services. This is the formula that I am using to calculate that:

WHILEPRINTINGRECORDS;
SHARED DATEVAR LAD := LAD ;
IF NOT (ISNULL ({movement_history.admission_date})) THEN LAD := {movement_history.admission_date} ELSE
LAD := LAD

where LAD is Last Admission Date

I need to be able to pull the information at least 1 month in advancse so that the managers will have enough time to get the surveys out. I tried using a date range parameter but it did not work. It pulled like it says, a range. Also went to Ken Hamady website and pulled this formula

DateAdd('m',3,{movement_history.admission_date}),

but wasn't sure how to incorporate it into the report.

Please help!

Using CR Version 8.5
Unix platform
SQL tables
-T

 

[synapsevampire]

Rather than using long text descriptions, the following tends to clearly identify requirements and options available:

Crystal version
Database/connectivity used
Example data
Expected output

I think what you want is to add the following to the Report->Edit Select5ion Formula->Record

month({movement_history.admission_date}) = month(dateadd("m",-3,{?dateparameter}))

The report will pull back everyone who had a service 3 months prior to the month of the date entered.

-k

 

[dgillz]

2 problems with SV's formula:

1) It will only extract the month that is equal to the 3rd prior month from the data parameter.

2) It will also extract this month regardless of the year.

I would change the formula as follows:

{movement_history.admission_date} in {?dateparameter} to Dateadd("m",-3,{?dateparameter})



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

http://www.trianglepartners.com

 

[lbass]

If you are trying to pull the customers with a last service date from three months ago, you will have trouble with accuracy if you try to pull the report 1 month in advance--before the three-month period has elapsed. Also, I'm not sure how your formula is calculating the most recent date. I think I would try to do a group selection formula. First group on {table.customerID} and then go to report->edit selection formula->GROUP and enter:

{movement_history.admission_date} =
maximum({movement_history.admission_date}, {table.customerID}) and
datediff("m",maximum({movement_history.admission_date},{table.customerID}), currentdate) = 3

-LB

 

[tektribe]

Thanks for your help. I am using the current date formula with a trim statement to pull the date and the LAD formula above to find the most recent service date. You were right lbass, I was having trouble with accuracy with the 1 month in advance! Being as though there are many date fields in the database, I created three different reports and used each of your suggestions in the reports. I passed them on to the users to check out. But looking at the results, dgillz's suggestion worked best! Thanks again -T

 

[synapsevampire]

Makes no sense to me.

I had thought that you wanted them for all years for the previous 3 months, and that the month numbers were to match.

"if I input the month of June, the report should pull all customers who received a service in March, regardless of the year"

Glad that it worked out though.

-k