There are 3 jobs running consecutively like A1, B1, and C1 every 5 minutes once this job will start execute.
Now I want to find the start and end time of each job set(ie. start time of A1 and End time of C1). In this case, I can only say that the 1st time job completes successfully means all the 3 jobs run successfully in the order A1, B1 and C1.
All 3 jobs should execute successfully and consecutively then only we can consider this as one set and assign one autonumber.
Let us assume that per hour it will execute around 12 times (i.e run every 5 mins gap). Now I want to know how many times the job executes successfully and how many time the job execution gets fail.
Then I want to know the time taken (i.e start & end time) for single execution of the jobs A1, B1 and C1, as I said this first execution, like this I need to know the each execution start &end time(time taken) .
My source is available like this.
WF_ID
WF_NAME
WF_RUN_ID
ST
ET
10
A
1
10:00
10:02
20
B
2
10:02
10:05
30
C
3
10:06
10:10
10
A
4
10:00
10:02
20
B
5
10:02
10:05
30
C
8
10:06
10:10
10
A
11
10:00
10:02
20
B
14
10:02
10:05
30
C
17
10:06
10:10
10
A
20
10:00
10:02
20
B
21
10:02
10:05
30
C
22
10:06
10:10
10
A
23
10:00
10:02
20
B
24
10:02
10:05
10
A
26
10:00
10:02
20
B
27
10:02
10:05
30
C
28
10:06
10:10
10
A
23
10:00
10:02
10
A
26
10:00
10:02
20
B
27
10:02
10:05
30
C
28
10:06
10:10
I want to make the qvd like the below table format. i.e the RUN_ID and Status field have to derive from the source table.
WF_ID
WF_NAME
WF_RUN_ID
ST
ET
RUN_ID
Status
10
A
1
10:00
10:02
1
Success
20
B
2
10:02
10:05
1
Success
30
C
3
10:06
10:10
1
Success
10
A
4
10:00
10:02
2
Success
20
B
5
10:02
10:05
2
Success
30
C
8
10:06
10:10
2
Success
10
A
11
10:00
10:02
3
Success
20
B
14
10:02
10:05
3
Success
30
C
17
10:06
10:10
3
Success
10
A
20
10:00
10:02
4
Success
20
B
21
10:02
10:05
4
Success
30
C
22
10:06
10:10
4
Success
10
A
23
10:00
10:02
0
Fail
20
B
24
10:02
10:05
0
Fail
10
A
26
10:00
10:02
5
Success
20
B
27
10:02
10:05
5
Success
30
C
28
10:06
10:10
5
Success
10
A
23
10:00
10:02
0
Fail
10
A
26
10:00
10:02
6
Success
20
B
27
10:02
10:05
6
Success
30
C
28
10:06
10:10
6
Success
If we bring the above format of table , we can find the start & end time by grouping the run_id.
