MaxDate-MinDate & sum difference

[yanios]

I'm running CR v10 and have an oracle database table that has history of events for each record. For ex: Enter, Approve, HR Update, Pay Update, Pension Update. I'm using the Minimum and Maximum date function to get the min and max date/time on records that are = Enter and Pension Update. (Don't care about any steps in between). I want to know when the record was entered and when it was updated by pensions (how long from enter to pensions) then total by dept# and get the average time it takes to enter and get to pensions. How can I get total time for each dept and calculate the average? Any help is appreciated.
My data looks like:
Dept #: 10
folder#: 123
enter date: 9/1/2005 10:00 am
approve date: 9/1/2005 11:00 am
hr update date: 9/2/2005 9:00 am
pay update date: 9/2/2005 1:30 PM
Pension update date: 9/3/2005 9:00 AM
My MINdate is 9/1/2005 10:00 am and my MAXdate 9/3/2005 9:00 am (MaxDate - MinDate = 2 days 23 hours)

 

[dgillz]

Are these 4 different date fields in your database?

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

http://www.gainfocus.biz

 

[lbass]

You should have a group on {table.dept}. If both "Enter" and "Pension Update" records are present for each department, then you should be able to create a formula like the following {@datediff}:

datediff("d",minimum({table.date},{table.dept}),maximum({table.date},{table.dept}))

Then create the following two formulas:

//{@accum} to be placed in the department group header or footer:
whileprintingrecords;
numbervar sumdiff := sumdiff + {@datediff};
numbervar counter := counter + 1;

//{@ave} to be placed in the report footer:
whileprintingrecords;
numbervar sumdiff;
numbervar counter;
sumdiff/counter

-LB

 

[yanios]

Thank you for the quick response. I am using 1 table but using it twice as an alais. I have a formula for MinDate d a formula for MaxDate and a formula for DateDiff. These seem to be ok. I can have records with only Enter or Enter and Approve or Enter, Approve, HR Upadate etc. I will try your suggestion. Thanks again.

 

[lbass]

In that case, I would use a record selection formula like:

{table.event} in ["Enter","Pension Update"]

Then change {@datediff} to:

if distinctcount({table.event},{table.dept}) = 1 then 9999 else
datediff("d",minimum({table.date},{table.dept}), maximum({table.date},{table.dept}))

Then change the accumulation formula to:

whileprintingrecords;
numbervar sumdiff;
numbervar counter;
if {@datediff} <> 9999 then
(
sumdiff := sumdiff + {@datediff};
counter := counter + 1
);

This will exclude those departments that do not have a pension update date, and at the same time, allow for instances where the dates might be the same and the difference is legitmately zero. The other approach you could take would be to subtract the enter date from the currentdate if there is no pension update date--but that would depend on your way of viewing your data.

-LB

 

[yanios]

LB,
Thank you. It worked but gave me totals at the end of the report. The @accum formula gave me running totals if placed in the Details & in group header/footer. I changed the @accum to @countdisplay
WhilePrintingREcords;
NumberVar Counter;
This gave me a record count by dept. Then I changed
@avg to @groupavg
WhilePrintingRecords;
NumberVar TheAvg;
TheAvg :={@datediff}/{@CountDisplay} This gave me average for the dept by max/min date. I also had to reset the counter by putting it the header record.
Thank you again for your help.