Need to sum total time 1

[AZdesertdog]

Crystal 8.5 MSSQL2k

I need a TOTAL amount of time that a ticket has the Value of "Waiting". I need the time to start from when the NewValue is changed to "Waiting" and to stop any time it is moved to another value and to start up counting time again if it is moved to "Waiting" any other time for the Ticket record. Need to have a total number of minutes for each ticket.

Data example:


Ticket OldValue NewValue ModifyDate NEED
GH1> 12345
D> NULL Open 1/1/05 07:00
D> Open Waiting 1/2/05 10:00
D> Waiting Open 1/3/05 10:00 1440mins
D> Open Assigned 1/4/05 08:00
D> Assigned Waiting 1/5/05 09:00
D> Waiting Resolved 1/5/05 11:00 1560mins

Total amout of "Waiting" time--->>> 3000mins

I dont' care about the running totals, just showing them for clarity. Just need the total number. Any help appreciated. Thanks!

-AZDesertDog

 

[dsWA]

Hi,

I would do something like this:

if {table.OldValue} = "Waiting" then
DateDiff("n",
{table.ModifyDate},
Previous({table.ModifyDate}))
else
0

This assumes that you cannot have records that go from state "Waiting" to "Waiting", and that the records are sorthed by date/time of change in state.

Derek.
BusinessObjects Consultant - Perth, Western Australia.

 

[lbass]

Create three formulas:

//{@reset} to be placed in GH#1:
whileprintingrecords;
numbervar mins := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar mins;
numbervar addmins;

if {table.OldValue} = "Waiting" then
mins := DateDiff("n",previous({table.ModifyDate}),{table.ModifyDate}) else
mins := 0;
addmins := addmins + mins;

//{@displmins} to be placed in the GF#1:
whileprintingrecords;
numbervar addmins;

-LB

 

[AZdesertdog]

Thanks guys! They both worked. I used LB's solution as it gave me the totals more easily.

One more tweak- It quite often occurs that a tickets last record has "Waiting" as the NewValue, meaning that it is still in waiting. Can you tweak the @accum so that if the last record is in waiting, it get the datediff from that last record to currentdatetime? I tried messing with OnLastRecord but couldn't get it to work with arguments. Thanks so much!

 

[lbass]

{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar mins;
numbervar addmins;

if {table.groupfield} <> next({table.groupfield}) and
{table.NewValue} = "Waiting" then
mins := DateDiff("n",{table.ModifyDate}, currentdatetime) else
if {table.OldValue} = "Waiting" then
mins := DateDiff("n",previous({table.ModifyDate}),{table.ModifyDate}) else
mins := 0;
addmins := addmins + mins;

-LB

 

[AZdesertdog]

Perfect! As always, LB you make my boss think I'm smarter than I really am!