CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
i_am_cris
i_am_cris

Reputation: 627

Use of window function?

I need to calculate the total time from different fields and rows but I need to exclude some times from the calculation. An example is easier to understand. I want for every row calculate order1_time_1+payment_time_1+payment_time_2+checkout_time_2 (if order2 have time_1=0 or time_2=0). Sometimes there are several order events but only one will have values in time_1 and time_2. I hope this makes sense. Any suggestion is appreciated. I think this can be solved with window functions but I just can't get it right.

My data (total_time showing calculation but only the result is needed):

id, Datetime, Event name, time_1, time_2, total_time (new field)
123, 2020-02-01 12:12, Order1, 30, 40, 30+20+50+40=140 
123, 2020-02-01 12:12, Order2, 0, 0, 30+20+50+40=140 
123, 2020-02-01 12:12, Payment, 20, 50, 30+20+50+40=140 
123, 2020-02-01 12:12, CheckOut, 60, 40, 30+20+50+40=140 

234, 2020-02-01 12:13, Order1, 0, 0, 300+20+50+50=420
234, 2020-02-01 12:13, Order2, 300, 400, 300+20+50+50=420
234, 2020-02-01 12:13, Payment, 20, 50, 300+20+50+50=420
234, 2020-02-01 12:13, CheckOut, 60, 50, 300+20+50+50=420

345, 2020-02-01 12:14, Order1, 30, 40, 30+20+50+50=150
345, 2020-02-01 12:14, Payment, 20, 50, 30+20+50+50=150
345, 2020-02-01 12:14, CheckOut, 60, 50, 30+20+50+50=150

Upvotes: 2

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

You want a sum() with conditional logic:

select t.*,
       sum(case when event_name in ('Order1', 'Payment') then time_1
                when event_name in ('Order2', 'CheckOut') then time_2
           end) over (partition by id) as total_time
from t;

Upvotes: 1

Related Questions