Complicated calculation 1

[puma51]

Hi

Using Crystal XI on SQLServer 2000

I have a table of orders with the fields org_name and order_date.
I want to identify the organisations that have had an order in a date range based on a parameter date field but who have not had an order in the 12 months prior to the latest date found in the parameter date range.

For example:-

Org Name Order Dates Parameter Value
org 1 23/03/05 01/10/06-31/12/06 False
14/07/06
31/10/06

org 2 23/03/05 01/10/06-31/12/06 True
31/10/06

Can anyone help?

Many thanks

puma

 

[dgillz]

who have not had an order in the 12 months prior to the latest date found in the parameter date range.

Please clarify. 12 months prior to the latest date in the range is Dec 31 2005. This means that in your case the dates where they have no order would be a grand total of 11 days (Dec 31 2005 to Jan 10 2006).

Is this really what you want? Or do you want the searched range to be 12 months before the oldest date in the range?

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

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[lbass]

Don't limit the dates of records in the record selection formula. Instead insert a group on {table.orgname} and then go to report->selection formula->GROUP and enter:

maximum({table.orderdate},{table.orgname}) = {?daterange} and
(
minimum({table.orderdate},{table.orgname}) = maximum({table.orderdate},{table.orgname}) or
nthlargest(2,{table.orderdate},{table.orgname}) < dateadd("m",-12,maximum({table.orderdate},{table.orgname}))
)

-LB