CODEWITHSUNDEEP
sqloracle-databasedatedifference
Nienke Bos
Nienke Bos

Reputation: 59

Difference between multiple dates

I am working in a database with multiple orders of multiple suppliers. Now I would like to know the difference in days between order 1 and order 2, order 2 and order 3, order 3 and order 4 and so on.. For each supplier on its own. I need this to generate the Standard Deviation for each supplier based on their days between orders.

Hopefully someone can help..

Upvotes: 0

Views: 61

Answers (2)

GMB
GMB

Reputation: 222412

You would typically use window function lag() and date arithmetics.

Assuming the following data structure for table orders:

order_id int primary key
supplier_id int
order_date date

You would go:

select 
    i.*,
    order_date 
        - lag(order_date) over(partition by supplier_id order by order_date) date_diff
from orders o

Which gives you, for each order, the difference in days from the previous order of the same supplier (or null if this is the first order of the supplier).

You can then compute the standard deviation with aggregation:

select supplier_id, stddev(date_diff) 
from (
    select 
        o.*,
        order_date 
            - lag(order_date) over(partition by supplier_id order by order_date) date_diff
    from orders o
) x
group by supplier_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

What you describe is lag() with aggregation:

select supplier,
       stddev(orderdate - prev_orderdate) as std_orderdate
from (select t.*,
             lag(orderdate) over (partition by supplier order by orderdate) as prev_orderdate
      from t
     ) t
group by supplier;

Upvotes: 1

Related Questions