I have a table with id, order sequence and date, and I am trying to add two columns, one with a difference in date function, and another with a status function that is reliant on the value of the difference in date.

Table looks like this:

The issue I am having is that, when I try to find the difference between the dates of each unique id, so that if it's the first order sequence, it should be null, if it's any subsequent order sequence, let's say 3, it will be the 3rd date - 2nd date. Now this all works with the code I have:

case 
    when ord_seq = 1 then null
    else ord_date - lag(ord_date) over (order by id)
end as date_diff,

However, this only works when the table is already ordered. If I jumble up the order that I input the table in, the values come out a little different. I figured it might be because "lag" function only takes the previous row's value, so if the previous row does not belong to the same id, and is not in chronological order, the dates won't subtract well.

My code looks like this at the moment:

select 
    id, 
    ord_seq, 
    ord_date,
    case 
        when ord_seq = 1 then null
        else ord_date - lag(ord_date) over (order by id)
    end as date_diff,
    case
        when ord_seq = 1 then 'New'
        when ord_date - lag(ord_date) over (order by id, ord_seq) between 1 and 200 then 'Retain'
        when ord_date - lag(ord_date) over (order by id, ord_seq) > 200 then 'Reactivated'
    end as status    
from t1

order by id, ord_seq, ord_date

My db<>fiddle

Am I using the correct function here? How do I find the difference in date between one unique ID, regardless of the order of the table? Any help would be much appreciated.

In case you want to see end table result (error is on id 'ddd', ord seq '2' and '3'):

Ordered Input:

Not Ordered Input:

When using this:

CodePudding user response：

You miss the partition by in your window frame definition. Here it is, working regardless of any table order:

select *,
   ord_date - lag(ord_date) over (partition by id order by ord_seq) as date_diff
from t1;

Please note however that database tables have no natural order that you can not rely upon and can not be considered ordered, no matter in what sequence the records have been inserted. You must specify explicitly an order by clause if you need a specific order.