CODEWITHSUNDEEP
sqlgoogle-bigquery
Secto Kia
Secto Kia

Reputation: 969

Summing field in other rows conditionally

I have table in the form like below:

Pilot Leg Duration Takeoff
John 1 60 9:00:00
John 2 60 9:00:00
John 3 30 9:00:00
Paul 1 60 12:00:00
Paul 2 30 12:00:00
Paul 3 30 12:00:00
Paul 4 60 12:00:00

And I am trying to figure out is a query to get the following:

Pilot Leg Duration Takeoff LegStart
John 1 60 9:00:00 9:00:00
John 2 60 9:00:00 10:00:00
John 3 30 9:00:00 10:30:00
Paul 1 60 12:00:00 12:00:00
Paul 2 30 12:00:00 13:00:00
Paul 3 30 12:00:00 13:30:00
Paul 4 60 12:00:00 14:00:00

So the 'LegStart' time is the 'TakeOff' time, plus the duration of prior legs for that pilot.

Now , to do this in SQL, I need to somehow add up the durations of prior legs for the same pilot. But for the life of me... I cannot figure out how you can do this because the pilots can have a variable number of legs, so joining doesn't get you anywhere.

Upvotes: 0

Views: 50

Answers (2)

Sergey Geron
Sergey Geron

Reputation: 10222

Try analytic SUM sum(duration) over (partition by pilot order by leg):

with mytable as (
  select 'John' as pilot, 1 as leg, 60 as duration, time '9:00:00' as takeoff union all
  select 'John', 2, 60, '9:00:00' union all
  select 'John', 3, 30, '9:00:00' union all
  select 'Paul', 1, 60, '12:00:00' union all
  select 'Paul', 2, 30, '12:00:00' union all
  select 'Paul', 3, 30, '12:00:00' union all
  select 'Paul', 4, 60, '12:00:00'
)
select
    *,
    time_add(takeoff, interval ifnull(sum(duration) over (partition by pilot order by leg ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) minute) as legstart
from mytable

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use a cumulative sum. The trick is including this in the

select t.*,
       sum(duration) over (partition by pilot order by leg) as running_duration,
       datetime_add(takeoff,
                    interval (sum(duration) over (partition by pilot order by leg) - duration) minute
                   ) as leg_start
from t;

Note: This assumes that takeoff is a datetime.

Upvotes: 2

Related Questions