Difference between the open to resolved time on records

[#1]

Hi ,Experts please  provide the solution for this case

Priority ID	Stage ID	Time	Case ID
1	1	01/01/2010 00:00:00	ABC112233445567050
1	2	01/01/2010 00:12:00	ABC112233445567050
1	3	01/01/2010 08:42:44	ABC112233445567050
1	4	01/01/2010 08:54:44	ABC112233445567050
1	5	01/01/2010 14:38:32	ABC112233445567050
1	1	01/01/2010 10:34:32	ABC112233445567095
1	2	01/01/2010 11:09:32	ABC112233445567095
1	3	01/01/2010 17:55:05	ABC112233445567095
1	4	01/01/2010 18:10:05	ABC112233445567095
1	5	01/01/2010 19:26:57	ABC112233445567095
1	1	01/01/2010 13:43:10	ABC112233445567113
1	2	01/01/2010 14:14:10	ABC112233445567113
1	3	01/01/2010 17:27:53	ABC112233445567113
1	4	01/01/2010 17:40:53	ABC112233445567113

Stage ID is the Field under the stage ID these are the records,

StageID 1=Open

StageID 2=Assign

StageID3=Development

StageID 4=Test

StageID 5=Resolved

I want to take  the difference in between the open to resolved  time in back end(Script).

[Gysbert_Wassenaar]

Something like this:

input:
LOAD
  [Priority ID]
, [Stage ID]
, Timestamp#([Time],'DD/MM/YYYY hh:mm:ss') as [Time]
, [Case ID]
INLINE [
    Priority ID, Stage ID, Time, Case ID
    1, 1, 01/01/2010 00:00:00, ABC112233445567050
    1, 2, 01/01/2010 00:12:00, ABC112233445567050
    1, 3, 01/01/2010 08:42:44, ABC112233445567050
    1, 4, 01/01/2010 08:54:44, ABC112233445567050
    1, 5, 01/01/2010 14:38:32, ABC112233445567050
    1, 1, 01/01/2010 10:34:32, ABC112233445567095
    1, 2, 01/01/2010 11:09:32, ABC112233445567095
    1, 3, 01/01/2010 17:55:05, ABC112233445567095
    1, 4, 01/01/2010 18:10:05, ABC112233445567095
    1, 5, 01/01/2010 19:26:57, ABC112233445567095
    1, 1, 01/01/2010 13:43:10, ABC112233445567113
    1, 2, 01/01/2010 14:14:10, ABC112233445567113
    1, 3, 01/01/2010 17:27:53, ABC112233445567113
    1, 4, 01/01/2010 17:40:53, ABC112233445567113
];

result:
load
  *
, if([Stage ID]=1,Time,peek(StartTime)) as StartTime
, if([Stage ID]=5,interval(Time-peek(StartTime),'hh:mm:ss')) as Duration
Resident input
order by [Case ID],[Time];

drop table input;

Replace the inline load with something that loads your real data.

[#3]

Hi Manoj

Maybe you can try this:

LOAD [Priority ID],

     [Stage ID],

     Time,

     [Case ID],

    if(previous(previous(previous(previous([Case ID])))) = [Case ID], date(date(Time, 'hh:mm:ss') - previous(previous(previous(previous(date(Time, 'hh:mm:ss'))))), 'hh:mm:ss')) As Difference

FROM

......

Let me know if this works for you.

Kind regards

[Anonymous]

gwassenaar

That is great !!!

I had a similar requirement earlier this week and my solution was ugly, lengthy and inefficient - although it did work.

Your suggestion is elegant, simple and I reckon will be efficient.  I just need to adapt it to my scenario.

Many Thanks,     Bill

[#5]

Thank you gwassenaar and jacobsbritt2 it worked  appreciate your early response.