Watcharin Chimmachuy Watcharin Chimmachuy - 7 days ago 4
SQL Question

Find record and group period when a status change at last

I have table rows like this.

acc start_dat end_dat n_type
aa 1/1/2017 2/2/2017 er
aa 2/2/2017 4/2/2017 er
aa 4/2/2017 1/3/2017 ok
aa 1/3/2017 12/3/2017 ok
aa 12/3/2017 15/4/2017 er
aa 15/4/2017 3/5/2017 er
aa 3/5/2017 5/5/2017 er
aa 5/5/2017 null ok
bb 12/2/2017 4/3/2017 er
bb 4/3/2017 20/3/2017 er
bb 20/3/2017 13/4/2017 er
bb 13/4/2017 4/5/2017 ok
bb 4/5/2017 7/6/2017 er
bb 7/6/2017 3/7/2017 er
bb 3/7/2017 null ok


I want to group the last status before change to ok with end_Dat = null for calculate date at start to end

acc start_dat end_dat duration_day
aa 12/3/2017 5/5/2017 52
bb 4/5/2017 3/7/2017 60


Are you have any idea to solve it?

Answer

You can count the number of "ok" records after each record and then use that for aggregation:

select acc, min(start_dat), max(end_dat),
       (max(end_dat) - min(start_dat)) as diff
from (select t.*,
             sum(case when n_type = 'ok' then 1 else 0 end) over (partition by acc order by start_dat desc) as numoks_after
      from t
     ) t
where numoks_after = 1 and n_type <> 'ok'
group by acc;