Date Diff

[bld21]

I use crystal reports 2008 with sql server 2005. I have 2 date fields. {customer_note.create_dte} and {account.create_dte}.

I only want to see accounts in which the customer_note.create_dte falls within 30 days of the account.create_dte.

Thanks to all!

 

[fisheromacse]

In your Select Expert, choose 'New' then select your field ({customer_note.create_dte}), open the dropdown to the left, if your field is a date field you will see the option 'Aged0To30Days'.

If it is not a date field, use the formula below to convert it and then use the created formula in place of your field.
You may need to adjust the formula, it is designed to take a YYYYMMDD value and convert it to MM/DD/YYYY.


//{@MakeitADate}
stringvar d := totext({customer_note.create_dte});
Date(val(left(d,4)),val(mid(d,5,2)),val(right(d,2)))

 

[lbass]

Go to report->selection formula->record and enter:

{customer_note.create_dte} <= {account.create_dte}+30

This assumes the note date field can only be greater than the account created date.

This would only return the row that contains the customer note create date. If you want the entire account if one note falls into the range, then you would not use that selection formula, but instead you would create a formula in the field explorer {@win30}:

if {customer_note.create_dte} <= {account.create_dte}+30 then 1

Then you would insert a group on {table.accountID} and go to report->selection formula->GROUP and enter:

sum({@win30},{table.accountID}) > 0

-LB