How to calculate the date difference between two records?

[achan275]

I need to do a report to tell how many parts are used per repair, while a repair is defined as all of a unit's record entries are generated within not more than 30 days apart. I am thinking of comparing the date of the records within a group(per unit) and see if it is more than 30, but not sure how to do the comparison between 2 records. Anyone would like to offer some idea?

Thanks in Advance,

AC

 

[mbarron]

The following formula should get you what you want.


//start of formula
datediff("d",minimum ({trans date}, {product}),Maximum ({trans date}, {product}))

//end of formula

Where:
{trans date} is the field you want to find the mix and max transaction date field and {product} is the unit field you want to check.

Mike
If you're not part of the solution, you're part of the precipitate.

 

[achan275]

Thanks Mike!!

The formula make sense to me and i created this formula myself:

whileprintingrecords;
dateVar crdate;
numbervar numdate:={createdate};
numbervar numyear;
numbervar nummonth;
numbervar numday;

numyear:=tonumber(left(totext(numdate),4));
nummonth:=tonumber(mid(totext(numdate),5,2));
numday:=tonumber(right(totext(numdate),2));
crdate:= date(numyear,nummonth,numday);

DATEDIFF("d",crdate,minimum(crdate,{@UNIT}))

But looks like it doesn't allow me to save, and gives me the error "The summary/running total field cannot be created"

Any idea?
the only grouping is on "UNIT" which a group of "UNIT" is a collection of all the part order entries for a distinct unit.

Thanks again
Albert

 

[mbarron]

Here is a simpler formula for creating your date. I'm assuming that {createdate} is a number representation of the date in yyyymmdd format.

stringvar crdate:=totext({createdate},0,"&quot

date (val(crdate[1 to 4]),val(crdate[5 to 6]), val(crdate[7 to 8]))

Where is this formula to be placed?
DATEDIFF("d",crdate,minimum(crdate,{@UNIT}))
If it would work, it would show the differnce between the first record in a group if it is a group header, for each date if it is in the details band, or the last detail's record if it's in the group footer

I would suggest creating the summary(s) then creating your datediff formula, adding the fields as needed from the list. Mike
If you're not part of the solution, you're part of the precipitate.

 

[dgillz]

on the date conversion issue, try this formula:

NumberToDate({NumDate})

This is a very easy formula that takes an 8 digit integer in a yyyymmdd format and converts it into a real date. NumbertoDate is not shipped with Crystal, but is available as a UFL at the CD website. Heres the link:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/ufltdate.exe.asp

Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com