So you want to find the duration of all steps that were "Client action/follow-up". Unless the next action is "Closed"?
If so, in Oracle you can find the value of the next statuses and dates with lead().
You can then filter on this in an outer query to exclude the values you don't want:
create table t (
incident_number int,
Start_Date date,
incident_status varchar2(30),
Incident_Status_Reason varchar2(30)
);
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
insert into t values (1,'2016-01-21 07:44:49','Resolved','Client Follow-up Required');
insert into t values (1,'2016-01-22 08:31:20','Assigned',NULL);
insert into t values (1,'2016-01-22 10:42:55','In Progress',NULL);
insert into t values (1,'2016-01-22 10:43:21','Pending','Client Action Required');
insert into t values (1,'2016-01-27 09:04:15','Assigned',NULL);
insert into t values (1,'2016-01-27 12:00:24','In Progress',NULL);
insert into t values (1,'2016-02-03 07:49:44','Resolved', 'Client Follow-up Required');
insert into t values (1,'2016-06-08 17:23:19','Closed','No Further Action Required');
select round(sum(next_st - start_date), 4) duration_in_days
from (
select start_date, Incident_Status_Reason,
lead(start_date) over (order by start_date) next_st,
lead(incident_status) over (order by start_date) next_status
from t
)
where Incident_Status_Reason in ('Client Follow-up Required','Client Action Required')
and next_status <> 'Closed';
DURATION_IN_DAYS
5.9635
So that's how you do it in Oracle. If you want a SQL Server solution, you'll need to ask on their equivalent of Ask Tom ;)