Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

select greater of two different date fields

Status
Not open for further replies.

koolskins

IS-IT--Management
Nov 22, 2003
79
US
Crystal 10 against an Informix database. I have a report grouping purchasing records by the expected delivery date so that the output is grouped by LESS THAN 30 DAYS, 30-60 DAYS, 60-90 DAYS, etc. The expected delivery date is subtracted from the data date to determine which group the record falls into. However, there are actually two different EXPECTED DELIVERY DATES that come into play, one from the purchase order and another one from an expediting record. How, do I select and group each row based on the greater date between these two tables? Also, the expediting record may or may not exist.

Thanks in advance.

Rw.
 
You didn't really give a record selection formula, but the way you would pull this dat out is this:

If isnull({ExpeditingDate}) then {PurchaseOrderDate} else maximum({ExpeditingDate},{PurchaseOrderDate})

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports
 
Okay, that fixed the field to display the greater of the two dates. The next request is to now make the displayed date part of the group criteria. The report now is grouping by the PO's original expected delivery date so that each record returned falls into the 30 day, 30-60 day, etc grouping. The formula for this group is:

if isnull({rp_head_line_700.exp_del_date}) then "Not Due" else
if {rp_head_line_700.exp_del_date}>currentdate then "Not Due" else
if (({rp_head_line_700.exp_del_date}-currentdate)*-1) < 30 then "Less Than 30 Days" else
if (({rp_head_line_700.exp_del_date}-currentdate)*-1) >29 and
(({rp_head_line_700.exp_del_date}-currentdate)*-1) <= 60 then "30-60 Days" else
if (({rp_head_line_700.exp_del_date}-currentdate)*-1) > 60 and
(({rp_head_line_700.exp_del_date}-currentdate)*-1) <= 90 then "60-90 Days" else
if (({rp_head_line_700.exp_del_date}-currentdate)*-1) >90 then "Greater than 90 Days"


I attempted to replace the {rp_head_line_700.exp_del_date} with the new formula {@expd_del} but now get a "grouping on a non-recurring field" error???
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top