selecting specific occurrences from a group SQL

Question

Data set looks like

id    statusid    statusdate    
100    22         04/12/2016    
100    22         04/14/2016
100    25         04/16/2016
100    25         04/17/2016
100    25         04/19/2016
100    22         04/22/2016
100    22         05/14/2016
100    27         05/19/2016
100    27         06/14/2016
100    25         06/18/2016
100    22         07/14/2016
100    22         07/18/2016

Task is to select the First time each status was logged. Number of unique times each status were logged.

Example : For Status 22

• First time status date: 04/12/2016
• Last time Status first date: 07/14/2016
• Number of Unique times it went to that status: 3

Answer 1

This is complicated than it looks. I assume you want the unique consecutive times for a given status. Use a difference of row numbers approach to classify consecutive rows into groups. Then get the row numbers in those groups. Finally aggregate to get the first day in the first and last group and the number of distinct groups.

select statusid
,max(case when rn_grp_asc=1 then statusdate end) as last_time_first_status
,max(case when rn_grp_desc=1 then statusdate end) as last_time_first_status
,count(distinct grp) as unique_times_in_status
from (select t.*
      ,row_number() over(partition by statusid order by grp,statusdate) as rn_grp_asc 
      ,row_number() over(partition by statusid order by grp desc,statusdate) as rn_grp_desc
      from (select t.*,row_number() over(order by statusdate)
           -row_number() over(partition by statusid order by statusdate) as grp
            from tbl t
           ) t
      ) t
group by statusid