Date Difference on Single Date - multiple entries

[smmedeiros]

Can anyone offer guidence on how I would calculate the difference between a datetime variable whereas it's a single field in the database.
I have 3 2 variables directly from the database: contact and activity date. The 3rd variable i create based on the contact details ManagerSort ( if member name is like a list of names i feed it).

My need is to determine the #hours works on a ticket based on adding all entries if ManagerSort = 1

Data Example:
Contact ActivityDate ManagerSort
Jamil 08/26/08 03:04 1
Abramov 08/26/08 08:30 1
System 08/26/08 12:30 0
Rourke 09/02/08 05:16 1
Rourke 09/03/08 05:19 1
System 09/06/08 00:07 0
Abramov 09/09/08 08:58 1
Flynn 10/09/08 06:22 1
Perry 10/09/08 11:48 1
Makwana 11/07/08 06:16 0
Makwana 11/10/08 05:46 0

Desired output would to create a new variable called TimeDateDiff

Contact ActivityDate ManagerSort TimeDateDiff(hours)
Jamil 08/26/08 03:04 1 -
Abramov 08/26/08 08:30 1 5
System 08/26/08 12:30 0 3
Rourke 09/02/08 05:16 1 168
Rourke 09/03/08 05:19 1 24
System 09/06/08 00:07 0 72
Abramov 09/09/08 08:58 1 72
Flynn 10/09/08 06:22 1 720
Perry 10/09/08 11:48 1 5
Makwana 11/07/08 06:16 0 696
Makwana 11/10/08 05:46 0 72


This would allow me to report on internal team hours spent 994, external team spent on ticket 843 hours.


I'm familar with the datediff formula, but, only when i have 2 independant date values. I'm in need of guidance on how I would accomplish this with only 1 date field with multiple values.

Thanks in advance for any ideas.

 

[Madawc]

You could pick up the relevant dates using a Variable or a Running Total. Use DateDiff on these values in a group footer, allowing for null results.

The use of Crystal's automated totals is outlined at FAQ767-6524.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[smmedeiros]

I'm using v11.
Thanks for the tip. However, the link for FAQ767-6524 seems a bit generalized and I was hoping for a bit more details from the numerous crystal experts within this forum. I'll start reading up on the basics.

 

[smmedeiros]

Closing this thread.

I found the solution in
"formula help please, time difference thread767-1500413"

Thanks for the help

 

[smmedeiros]

So, I thought I was all set. I am extremely close to what I need. Based on the older thread 767-1500413 i was able to calculate the time differences between entries. However, I am now in need of help to reset the formula between records.

I have chgticket# that has multiple activity log entries and multiple date entries. I have the new variable for my accumulated days. How do I reset the value between groups (chg#)?

Thanks

 

[lbass]

Please show the formula you are currently using.

-LB

 

[smmedeiros]

datetimevar prev1;
datetimevar curr1;
prev1 := curr1;
if {@ManagerSort} = 1 then
curr1 := {@ActivityDate};
datediff("n",prev1,curr1);

 

[lbass]

Try adding a reset formula in the group header:

whileprintingrecords;
datetimevar prev1;
datetimevar curr1;
if not inrepeatedgroupheader then (
prev1 := datetime(0,0,0,0,0,0);
curr1 := datetime(0,0,0,0,0,0)
);

-LB

 

[smmedeiros]

Got it working..Thank You for the guidance.