Afternoon All,

I'm pulling my non-existent hair out on this...

Trying to work out the average duration a specific task occurs from start to finish.

Simplified version of table:

I'm pretty new to SQL, so it's probably something really simple - I have a feeling that I would either need to use LAG or Union (or possibly both!) but not sure if I'm overthinking it!

There are a number of different lines in the table with the same ID but these will be ignored under a where clause, so will just be focusing on the Allocate/Replenish rows.

Any help will be appreciated!

CodePudding user response：

You could use lead/lag, but you can also use aggregation, which could be conditional to effectively ignore any that only have one of the codes:

select id,
  max(case when code = 'Replenish' then dstamp end)
    - min(case when code = 'Allocate' then dstamp end) as diff
from your_table
group by id

But you can't directly average an interval. One option is to convert the intervals to the equivalent number of seconds, which you can do with extract(), maybe using a CTE to avoid repeating the calculation:

with cte (id, diff) as (
  select id,
    max(case when code = 'Replenish' then dstamp end)
      - min(case when code = 'Allocate' then dstamp end)
  from your_table
  group by id
)
select id, diff,
  extract(day from diff) * 86400
      extract(hour from diff) * 3600
      extract(minute from diff) * 60
      extract(second from diff) as diff_seconds
from cte

then average those numbers, and convert that back to an interval (or whatever data type you need):

with cte (id, diff) as (
  select id,
    max(case when code = 'Replenish' then dstamp end)
      - min(case when code = 'Allocate' then dstamp end)
  from your_table
  group by id
)
select avg(
  extract(day from diff) * 86400
      extract(hour from diff) * 3600
      extract(minute from diff) * 60
      extract(second from diff)
  ) * interval '1' second as avg_diff
from cte

fiddle

If you have an ID that only has 'Allocate' then that won't affect the result; and this should work even if you didn't filter out the other code values first.