i have data with 3 columns: (column1,col2,col3)
as (start date, incident status (Assigned/Inprogress/Resolved), incident_status_reason as (Client Action/ Client Follow) as below values:

Start Date incident_status Incident_Status_Reason
2016-01-21 07:27:17.000 Assigned NULL
2016-01-21 07:36:47.000 In Progress NULL
2016-01-21 07:44:49.000 Resolved Client Follow-up Required
2016-01-22 08:31:20.000 Assigned NULL
2016-01-22 10:42:55.000 In Progress NULL
2016-01-22 10:43:21.000 Pending Client Action Required
2016-01-27 09:04:15.000 Assigned NULL
2016-01-27 12:00:24.000 In Progress NULL
2016-02-03 07:49:44.000 Resolved Client Follow-up Required
2016-06-08 17:23:19.000 Closed No Further Action Required

i;e., Status Resolved will be shown at last,else if it shows in middle and next we see Assigned status after it, then it means it got reopened and will be resolved after everything done.

Here the total Client time: Client Action (Difference of : 2016-01-22 10:43:21.000 and 2016-01-27 09:04:15.000 + Client follow (Diff of: 2016-01-21 07:44:49.000 and 2016-01-22 08:31:20.000) and client follow(Diff of:2016-02-03 07:49:44.000 and 2016-06-08 17:23:19.000)....

Now, i have to remove the client time from total client time, once it has reached at last with status (resolved), but my Query is calculating the total time even from last resolve to closed/No further Action required( which should not be done)

EX: For above example,The Time duration between these has to be removed from my query if status is "resolved" (which need to be the last resolved of all of them).

2016-02-03 07:49:44.000 Resolved Client Follow-up Required
2016-06-08 17:23:19.000 Closed No Further Action Required

Below is the Query i wrote, but its calculating the duration even after resolved.. till status closed..Request to modify my Query where it doesn't calculates the Client duration after last resolved. Note: I cannot write any Other Declare statement etc in Query, i have to restructure my existing query to fit in it.

WITH MAIN AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY a.Incident_Number,a.start_date),a.start_date,a.Incident_Number,a.Incident_Status,a.Incident_Status_Reason
FROM dbo.XYZ a
)
SELECT
MAIN.Incident_Number,sum(nex.start_date-MAIN.start_date)/60 as Client_Duration
FROM MAIN
LEFT JOIN MAIN nex ON nex.rownum = MAIN.rownum + 1
where
(Main.Incident_Status_Reason='Client Action Required'
or Main.Incident_Status_Reason='Client Follow-up Required'
and Main.Incident_Number=nex.Incident_Number
GROUP BY MAIN.Incident_Number

Request to provide solution asap, since urgent requirement.. Thanks in Advance

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