Dear Sir,
TASKID | EMPLOYEECODE| SLOT_ACTION | date
1 A S 08/jan/2020 09:30:32 am
1 A p 08/jan/2020 02:10:20 pm
1 A S 09/jan/2020 09:50:50 am
1 A P 09/jan/2020 12/50:58 pm
1 A S 10/jan/2020 09:30:32 am
1 A P 10/jan/2020 12/50:58 pm
1 A S 11/jan/2020 09:30:32 am
1 A p 11/jan/2020 02:10:20 pm
1 A S 12/jan/2020 09:50:50 am
1 A P 12/jan/2020 12/50:58 pm
1 A S 13/jan/2020 09:30:32 am
1 A P 13/jan/2020 12/50:58 pm
1 A S 14/jan/2020 09:30:32 am
1 A p 14/jan/2020 02:10:20 pm
1 A S 15/jan/2020 09:50:50 am
1 A P 15/jan/2020 12/50:58 pm
1 A S 16/jan/2020 09:30:32 am
1 A P 16/jan/2020 12/50:58 pm
1 A S 17/jan/2020 09:30:32 am
1 A p 17/jan/2020 02:10:20 pm
1 A S 18/jan/2020 09:50:50 am
1 A P 18/jan/2020 12/50:58 pm
1 A S 19/jan/2020 09:30:32 am
1 A P 19/jan/2020 12/50:58 pm
. . . .
. . . .
like that 100 records there
2nd last and last record is
1 A S 10/feb/2020 09:30:58 am
1 A E 10/feb/2020 05:20:25 pm
they want calculate time.
they ask how much time taken to complete takid 1 for employee A?
Where
S - Start work
P - Pause work
E - End or completed work
A - Employee code
1 - Taskid
Assuming that the sequence is always
S -> P -> S -> P -> ... -> S -> E
i.e. every S is followed by a P or E, it's just a matter of
* Pairing up the starts with pauses/ends
* Subtracting the start from the end
* Summing the total
There's many ways you could do this. Here's a pivot approach to get you started:
* Assign a row_number() to each S and P/E, ordered by date
* Map E -> P
* Pivot the date for each emp, task, and rn
with rws as (
select 1 id, 'A' emp, 'S' state, to_date ( '08/jan/2020 09:30:32 am', 'dd/mon/yyyy hh:mi:ss am' ) dt from dual union all
select 1 id, 'A' emp, 'P' state, to_date ( '08/jan/2020 02:10:20 pm', 'dd/mon/yyyy hh:mi:ss am' ) from dual union all
select 1 id, 'A' emp, 'S' state, to_date ( '09/jan/2020 09:50:50 am', 'dd/mon/yyyy hh:mi:ss am' ) from dual union all
select 1 id, 'A' emp, 'P' state, to_date ( '09/jan/2020 12/50:58 pm', 'dd/mon/yyyy hh:mi:ss am' ) from dual union all
select 1 id, 'A' emp, 'S' state, to_date ( '10/jan/2020 09:30:32 am', 'dd/mon/yyyy hh:mi:ss am' ) from dual union all
select 1 id, 'A' emp, 'E' state, to_date ( '10/jan/2020 12/50:58 pm', 'dd/mon/yyyy hh:mi:ss am' ) from dual
), rks as (
select id, emp,
dt,
case
when state in ('E','P') then 'P'
else 'S'
end state,
row_number() over (
partition by case
when state in ('E','P') then 'P'
else 'S'
end
order by dt
) rn
from rws r
)
select * from rks
pivot (
min ( dt ) for state in ( 'S' st, 'P' en )
);
ID EMP RN ST EN
1 A 1 08-JAN-2020 09:30:32 08-JAN-2020 14:10:20
1 A 2 09-JAN-2020 09:50:50 09-JAN-2020 12:50:58
1 A 3 10-JAN-2020 09:30:32 10-JAN-2020 12:50:58 All you need to do from here is group and sum the differences.