create view usm_vw_request_status as
select
    request_status_id,
    cast(request_id as varchar) as request_id_char,
    request_id,
    cast(subscription_detail_id as varchar) as subscription_detail_id_char,
    subscription_detail_id,
    status,
    status_date,
    user_id,
    status_old,
    (select max(status_date) from usm_request_status Submitted where status = 200 and Submitted.subscription_detail_id is null and Submitted.request_id=urs.request_id) as submitted_date,
    (select max(status_date) from usm_request_status Approval where status = 999    and Approval.subscription_detail_id is null and Approval.request_id = urs.request_id) as approval_date,
    (select max(status_date) from usm_request_status Fulfilled where status = 2000 and Fulfilled.subscription_detail_id is null and Fulfilled.request_id=urs.request_id) as fulfilled_date,
    (select max(status_date) from usm_request_status Completed where status = 2 and Completed.subscription_detail_id is null and Completed.request_id=urs.request_id) as completed_date,
    (select max(status_date) from usm_request_status Fulfillment_Cancelled where    status = 1999 and Fulfillment_Cancelled.subscription_detail_id is null and Fulfillment_Cancelled.request_id=urs.request_id) as fulfillment_cancelled_date,
    (select max(status_date) from usm_request_status Current_State,usm_request ur where ur.request_id = Current_State.request_id and ur.status=Current_State.status and Current_State.status_old <> 4000 and Current_State.subscription_detail_id is null and Current_State.request_id=urs.request_id) as enter_current_state_at,
    Datediff(s,(select max(status_date) from usm_request_status Submitted where status = 200 and Submitted.subscription_detail_id is null and Submitted.request_id=urs.request_id),(select max(status_date) from usm_request_status Approval where status = 999 and Approval.subscription_detail_id is null and Approval.request_id = urs.request_id)) as approval_time,
    Datediff(s,(select max(status_date) from usm_request_status Approval where status = 999 and Approval.subscription_detail_id is null and Approval.request_id=urs.request_id),(select max(status_date) from usm_request_status Fulfilled where status = 2000 and Fulfilled.subscription_detail_id is null and Fulfilled.request_id = urs.request_id)) as fulfilled_time,
    Datediff(s,(select max(status_date) from usm_request_status Submitted where status = 200 and Submitted.subscription_detail_id is null and Submitted.request_id=urs.request_id),(select max(status_date) from usm_request_status Completed where status = 2 and Completed.subscription_detail_id is null and Completed.request_id = urs.request_id)) as completed_time,
    Datediff(d,(select max(status_date) from usm_request_status Current_State,usm_request ur where ur.request_id = Current_State.request_id and ur.status=Current_State.status and Current_State.status_old <> 4000 and Current_State.subscription_detail_id is null and Current_State.request_id=urs.request_id),getdate()) - ((dbo.usm_fn_get_request_holdtime((select distinct ur.request_id from usm_request_status Current_State,usm_request ur where ur.request_id = Current_State.request_id and ur.status=Current_State.status and Current_State.status_old <> 4000 and Current_State.subscription_detail_id is null and Current_State.request_id=urs.request_id),-1,(select distinct ur.status from usm_request_status Current_State,usm_request ur where ur.request_id = Current_State.request_id and ur.status=Current_State.status and Current_State.status_old <> 4000 and Current_State.subscription_detail_id is null and Current_State.request_id=urs.request_id)))/86400) as duration_in_current_state
from usm_request_status urs
GO
GRANT SELECT ON  [dbo].[usm_vw_request_status] TO [usmgroup]
GO