sql server - SQL Ranking based on value in column -

i trying create ranking based on value inside of results. table consists of id of job, date, , status name. trying create 3 additional fields of "desired group", "desired rank", , "date diff". trying use word "re-initiated" statusname column restart clock on when measure date diff. final goal date diff field working based on grouping/ranking.

so, first "re-initiated" happened on july 22 , response happened on july 22 - date diff zero. second "re-initiated" happened on 9/23 , last response on 9/26 date diff of 3 days.

any ideas wonderful!!

id  date    statusname  desired group   desired rank    date diff 213 7/22/15 re-initiated    1           1    213 7/22/15 outside of area 1           2               0 213 9/23/15 re-initiated    2           1    213 9/24/15 sent field   2           2    213 9/26/15 no cost share   2           3               3 

you can calculate desired group cumulative sum. rank , diff come that:

select t.*,        row_number() on (partition id, grp order date) rnk,        datediff(day, min(date) on (partition id, grp), date) diff (select t.*,              sum(case when statusname = 're-initiated' 1 else 0 end) on (partition id order date) grp       t      ) t; 

note puts difference in dates on every row. can use case statement if want rows.