Datediff question

[BradCustom]

I'm working with Crystal 2008 and trying to find the Date Difference between the End Date of the first record and the Start Date of the next record. I found a thread where someone else was trying to do something similar. Each record has a Start and End Date.

Below is the formula I've tried.

if {Transaction_Data.Transaction_Start}= next({Transaction_Data.Transaction_Start}) then
datediff("n",{Transaction_Data.Transaction_End},next({Transaction_Data.Transaction_Start}))

The result of this formula is 0 for every line.

Thanks for the help.

 

[BradCustom]

I also tried this formula with the same result, all zeros.

if {Transaction_Data.Transaction_End} = previous({Transaction_Data.Transaction_End})
and
{Transaction_Data.Transaction_Start} = next({Transaction_Data.Transaction_Start}) then
datediff("n",{Transaction_Data.Transaction_Start},previous({Transaction_Data.Transaction_End}))

 

[lbass]

The "n" is for minutes, so are these datetimes? Otherwise use "d" for days.

Also don't know why you are comparing the whether the start and ends are the same or not. Usually you would be concerned whether you are comparing dates for the same group, so you might do a comparison like this:

if {table.groupfield}=previous({table.groupfield}) then
datediff("d",previous({table.end}),{table.start})

-LB

 

[BradCustom]

I'll try to clearify what I'm trying to do with this calculation.

The report has two groups the first is Work Center and the second is Work Date which is sorted in ascending order for each year.

The detail only has two fields Transaction_Start and Transaction_End, I'm trying to find the time difference in minutes between the Transaction_End time on the first record and the Transaction_Start time on the second record. I'd like to do this calculation for every record. Ultimately I'll do an average for each Work Center which will tell us the average que time between the end of one job and the start of another.

Hope this helps to explain what I'm trying to do.
Thanks for your help.

 

[lbass]

So you should try the formula I showed you for the datediff, only using "n". Just substitute the workdate field for {table.groupfield}.

-LB

 

[BradCustom]

This question may seem simple but I'm missing your meaning of {table.groupfield}.

Thanks for your help.

 

[MCuthill]

BradCustom,

LBass is most likely referring to the Field upon which your Group Level is based; just using "generalized" jargon.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[BradCustom]

Thanks for your input MCuthill, if I use the Work Date field the calculation may not work because there can be multiple records that have the same Work Date. The program codes all Work Dates the same, for instance 01/04/2012 would be 01/04/2012 12:00:00AM for every record entered on 01/04/2012.

Perhaps this doesn't matter?

Thanks for your help.

 

[lbass]

The formula should probably include the work center, too, as in:

if {table.workcenter}=previous({table.workcenter}) and
{table.workdate} = previous({table.workdate}) then
datediff("n",previous({table.end}),{table.start})

-LB

 

[BradCustom]

Ok, I was able to get lbass's formula to work. by adding the Work Date as a field in the detail section.

Thanks for your help!!

 

[lbass]

If there is a group#2 on workdate, then you shouldn't have to add it to the details section, since it would have the same value for every record in the group.

-LB

 

[BradCustom]

I've noticed one problem with this formula. If the End Date is not the same day as the Start Date it returns a value of 0.

For example:

Start Date End Date DateDiff
1/4/2012 8:00:00AM 1/4/2012 04:00:00PM
1/5/2012 8:00:00AM 1/5/2012 01:00:00PM 0.00

It should return the number of minutes between 1/4/2012 04:00:00PM and 1/5/2012 8:00:00AM

Thanks for your help!

 

[lbass]

I don't see any reason for a zero in this case, so I'm wondering where in the body of the report you are extracting this. Can you provide the groups/records around this?

Also please show your exact formula.

-LB

 

[BradCustom]

Below is the structure of the report.

GH1 Work Center
GH2 Work Date

Detail Fields
{Transaction_Data.Transaction_Start}
{Transaction_Data.Transaction_End}
{Transaction_Data.Work_Date}
{@QueTime} (this is the datediff formula)

I think the reason for the error is because of the following senerio:

Start Date                 End Date       DateDiff       Work Date
3/19/2010 9:27:28PM  3/20/2010 2:28:40AM  26.00  3/19/2010 12:00AM
3/20/2010 6:43:13AM  3/20/2010 7:52:20AM   0.00  3/20/2010 12:00AM
3/22/2010 7:10:40AM  3/22/2010 10:12:41AM  0.00  3/22/2010 12:00AM
3/22/2010 10:13:08AM 3/22/2010 11:57:09AM  1.00  3/22/2010 12:00AM

Let me know if you need any additional information.
Thanks!

 

[lbass]

Okay, the problem is that you want to ignore the workdate group--not sure why you are grouping on it then--but just change the formula to:

if {table.workcenter}=previous({table.workcenter}) then
datediff("n",previous({table.end}),{table.start})

-LB

 

[BradCustom]

Thanks, that change works fine.