Window functions + self join to solve?

I want to display how many days an item was in each stage. We have many items, so just showing one ID for now for simplicity.
For first stage it should be action_date -created time.
For last stage it should be today – last action_date (these are uncompleted tasks, that is why we count fromt oday)
For all other, it’s simply the difference in action_dates.
How do you get from top table to bottom with SQL? Thanks a lot in advance!

Answer

One way uses lead() and union all:

select id, old_value, datediff(day, created, action_date) as days_in_stage
from t
where not exists (select 1
                  from t t2
                  where t2.id = t.id and t2.action_date < t.action_date
                 )
union all
select new_value,
       date_diff(day,
                 action_date,
                 lead(action_date, 1, getdate()) over (partition by id order by action_date)
                ) as days_in_stage
from t;

Or a cleaner solution (in my opinion) unpivots the data:

select t.id, v.value,
       datediff(day,
                coalesce(max(fromd), created),
                coalesce(max(tod), getdate)
               ) as days_in_stage
from t cross apply
     (values (t.old_value, t.action_date, null),
             (t.new_value, null, t.action_date)
     ) v(value, tod, fromd)
group by t.id, v.value;