I have an table in Oracle
shift_id | timestamp_(oracle) | type_ |
---|---|---|
00000001 | 17/05/2022 08:00 | 0001 |
00000001 | 17/05/2022 09:00 | 0002 |
00000001 | 17/05/2022 09:15 | 0003 |
00000001 | 17/05/2022 12:00 | 0002 |
00000001 | 17/05/2022 13:00 | 0003 |
00000001 | 17/05/2022 15:00 | 0004 |
type 1 is starttime, type 2 is pause, type 3 is continue after pause, type 4 is end_time
I want to calculate the effective shift time, one way to do this in my opinion is to calculate the sum of all start and end times and substract them, for example:
(sum(type 0002) sum (type 0004)) - (sum (type 0001) sum (type 0003))
But how to do this in oracle sql?
I've tried: select shift_id, sum(to_number_to_char(timestamp_,'sssss') where (type_ = 0002 or type is 0004) group by shift_id
Then I get a result like 00000001, 82442 But the sum is not the right sum, it only calculates with seconds after midnight.
Question: How to get a result like: 00000001, 05:45
CodePudding user response:
You can use LAG window function to calculate the time differencea and thn SUM it to get the total amount -
CALC AS (SELECT d.*,
EXTRACT(DAY FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) * 24 * 60
EXTRACT(HOUR FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) * 60
EXTRACT(MINUTE FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) tm
FROM data d)
SELECT shift_id,
TRUNC(ROUND(SUM(CASE WHEN type_ <> '0003' then tm else null end))/ 60) || ':' ||
MOD(ROUND(SUM(CASE WHEN type_ <> '0003' then tm else null end)), 60) tot_tm
from calc
GROUP BY shift_id;
CodePudding user response:
Doing a LAG gives you access to the current and previous values
select
shift_id,
typ,
tstamp,
lag(typ) over ( order by tstamp ) prev_typ,
lag(tstamp) over ( order by tstamp ) prev_tstamp
from ...
Once you have that, then you can work out intervals as required, eg
select
shift_id,
min(case when typ = 1 then tstamp end ) start_time
max(case when typ = 4 then tstamp end ) end_time
sum(case when typ in (2,4) then tstamp - prev_tstamp end )
from
( < above >
group by shift_id
or similar depending on how you want to slice and dice