Date Diff Aggregate & Avg time (days,hrs,mins)

[IceRuby]

Access 2007 database, Crystal XI

Need to calculate difference between two date/times in days,hrs, mins also calculate an average if item critera has meet "start" & "archive" status. See example

Each record has a unique id. Therefore have applied a group to ID.

Created a formula to identify True in meeting Start or Archive critera. I need to keep these as separate lists so may confirm that each ID has both a True response for meeting Start and Archive criteria.

@start formula {item} = "Start"
@archive formula {item} = "Archive"

Group - ID XXXX
Start Date End Date @start @archive
5/5/09 12:00:00 5/5/09 12:00:00 True False
29/5/09 12:00:00 29/5/09 12:00:00 False True
3/6/09 12:00:00 3/6/09 12:00:00 False True

I need to calculate difference for only those records that have both a Start and Archive True response for each grouped ID. How do I create a formula to validate this requirement, aggregate of the time difference between all dates and also calculate an average?

Thanks in advance

 

[lbass]

By "record," I guess you really mean ID, which contains multiple records. If the criteria are met, are you looking for the difference between the first and last dates for that group? Is the average then NOT per ID, but for the report as a whole (across multiple ID groups)?

-LB

 

[IceRuby]

Sorry yes, each unique record = ID.

I need the difference between the first and last dates for the group so can calculate aggregate values for report as whole.

e.g.
Group - ID 1111
Start Date End Date @start @archive
5/5/09 12:00:00 5/5/09 12:00:00 True False
29/5/09 12:00:00 29/5/09 12:00:00 False True
3/6/09 12:00:00 3/6/09 12:00:00 False True
difference (3/6/09 12:00:00 -5/5/09 12:00:00)dd.hrs.mm
(criteria met as @start true & @archive true)

Group - ID 2222
Start Date End Date @start @archive
5/5/09 12:00:00 5/5/09 12:00:00 True False
29/5/09 12:00:00 29/5/09 12:00:00 False False
3/6/09 12:00:00 3/6/09 12:00:00 False False
(criteria not met) no diff.

Group - ID 3333
Start Date End Date @start @archive
5/5/09 12:00:00 5/5/09 12:00:00 True False
29/5/09 12:00:00 29/5/09 12:00:00 False False
3/6/09 12:00:00 3/6/09 12:00:00 False False
difference (3/6/09 12:00:00 -5/5/09 12:00:00)dd.hrs.mm
(criteria met as @start true & @archive true)

Aggregate total (sum groupid 1111 & 3333)
number of items met criteria to
calc average

 

[lbass]

Create these formulas:

whileprintingrecords;
numbervar secs;
numbervar cnt;
if maximum({@Start},{table.ID}) = true and
maximum({@Archive},{table.ID}) = true then (
secs := datediff("s",minimum({table.startdate},{table.ID}),maximum({table.enddate},{table.ID}));
cnt := cnt + 1
);

Then in the report footer, use:

whileprintingrecords;
numbervar secs;
numbervar cnt;
secs/cnt

Then use the faq767-3543 to convert the result to dd:hh:mm, and set dur := secs/cnt. You might have to use:

dur := round(secs/cnt,0);

-LB

 

[IceRuby]

Thanks.

The dd:hh:mm differences per {table.id} are being calculated correctly.

I have a new requirement - Out of all of the {table.id} dd:hh:mm differences how can I identify the minimum and maximum dd:hh:mm.

That is the minimum dd:hh:mm for report as whole?

Appreciate your help

 

[lbass]

You don't need the average? Anyway, add to this formula:

whileprintingrecords;
numbervar secs;
numbervar cnt;
numbervar min;
numbervar max;
if maximum({@Start},{table.ID}) = true and
maximum({@Archive},{table.ID}) = true then (
secs := datediff("s",minimum({table.startdate},{table.ID}),maximum({table.enddate},{table.ID}));
cnt := cnt + 1
);
if cnt = 1 then
min := secs else
if secs < min then
min := secs;
if secs > max then
max := secs;
secs;

Then in the report footer, use formulas like this:

whileprintingrecords;
numbervar min;

whileprintingrecords;
numbervar max;

Use the faq767-3543 to convert min or max in each of these formulas to string format dd:hh:mm.

-LB

 

[IceRuby]

Great thanks very much.

I do still need to calc average but cannot get total of variable secs.

I've tried minor changes to formula but to no avail

How can I get this with new formula?

 

[IceRuby]

I re-tried your original formula. The result of variables secs/cnt was returning a very small value.

whileprintingrecords;
numbervar secs;
numbervar cnt;
if maximum({@Start},{table.ID}) = true and
maximum({@Archive},{table.ID}) = true then (
secs := datediff("s",minimum({table.startdate},{table.ID}),maximum({table.enddate},{table.ID}));
cnt := cnt + 1
);

Then in the report footer, use:

whileprintingrecords;
numbervar secs;
numbervar cnt;
secs/cnt

So I and commented numbervar cnt; and secs/cnt; for it to return the sec value. It returned the last "true" datediff calculation for min / max dates formula - 769.

To get the average I need the sum of all calculated datediffs.

e.g
Group - ID 1111
Start Date End Date @start @archive
5/5/09 12:00:00 5/5/09 12:00:00 True False
29/5/09 12:00:00 29/5/09 12:00:00 False True
3/6/09 12:00:00 3/6/09 12:00:00 False True
difference (3/6/09 12:00:00 -5/5/09 12:00:00)28.21.32(est)
(criteria met as @start true & @archive true)

Group - ID 2222
Start Date End Date @start @archive
5/5/09 12:00:00 5/5/09 12:00:00 True False
29/5/09 12:00:00 29/5/09 12:00:00 False False
3/6/09 12:00:00 3/6/09 12:00:00 False False
(criteria not met) no diff.

Group - ID 3333
Start Date End Date @start @archive
5/5/09 12:00:00 5/5/09 12:00:00 True False
29/5/09 12:00:00 29/5/09 12:00:00 False False
3/6/09 12:00:00 3/6/09 12:00:00 False False
difference (3/6/09 12:00:00 -5/5/09 12:00:00)28.13.19
(criteria met as @start true & @archive true)

Need to sum Group 1111 difference 28.21.32 & Group 3333 difference 28.13.19 and divide by count.

The count function is working well.

I have the whileprinting records numbervar (secs & cnt) in RF and whileprinting records date diff calc formula in Group #1.

Thanks

 

[lbass]

This formula ({@accum}) should be in the group section, and I'm sorry, it should be changed to:

whileprintingrecords;
numbervar secs;
numbervar cnt;
if maximum({@Start},{table.ID}) = true and
maximum({@Archive},{table.ID}) = true then (
secs := [red]secs + [/red]datediff("s",minimum({table.startdate},{table.ID}),maximum({table.enddate},{table.ID}));
cnt := cnt + 1
);

The {@ave} formula belongs in the report footer (secs/cnt).

-LB

 

[IceRuby]

Thats excellent!!

I made a minor change removing cnt from @accum formula as it was doubling.

Thanks very much for your time

 

[lbass]

Where are you accumulating cnt then? You should be able to leave it there--just make sure you don't have any old formulas containing cnt still in the group section.

-LB

 

[IceRuby]

You are right. I still had the count within the max and min formula.
So removed it from @accum formula.
Thanks very much again.
Learnt alot from your help.