Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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.
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
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
Thank you gwassenaar and jacobsbritt2 it worked appreciate your early response.