ALTER FUNCTION [dbo].[FUN_ASDK_STATUS_TIME] (
	@i_case_id INT,
	@i_status_id INT,
	@i_with_calendar INT -- 0= Calculo sin Calendario   1= Calculo con Calendario 
) RETURNS INT AS
BEGIN
	declare @Tiempo int, @Tiempo2 int;

	IF(@i_with_calendar=0)
	BEGIN
		with Tpe as( 
		select himo_id,achi_item_id, himo_old_value, himo_new_value, achi_created,ROW_NUMBER() OVER(ORDER BY himo_id) ord from asdk_hist_modify
		join asdk_action_hist on asdk_hist_modify.himo_action_hist_id = asdk_action_hist.achi_id 
		where himo_field_modified='STATUS'
		and achi_item_id= @i_case_id --ID Caso
		)


		select @Tiempo=SUM(DATEDIFF(minute,tmp.ini,tmp.fin))
		from(
			select A.himo_id,A.achi_item_id, A.himo_old_value, A.himo_new_value, A.achi_created ini,ISNULL(B.achi_created,GETDATE()) fin
			from Tpe A
			left join Tpe B on A.ord = B.ord-1
			)tmp
		WHERE tmp.himo_new_value=@i_status_id --ID Estado
		group by tmp.achi_item_id;

			--Llamadas de servicio
			IF(@Tiempo is null 
			and (select count(*) from afw_status_transition where sttr_code_fin=@i_status_id)=0
			and (select stat_app_category from afw_status where stat_id=@i_status_id )=4  )
			BEGIN

				with Tpe as( 
				select himo_id,achi_item_id, himo_old_value, himo_new_value, achi_created,ROW_NUMBER() OVER(ORDER BY himo_id) ord from asdk_hist_modify
				join asdk_action_hist on asdk_hist_modify.himo_action_hist_id = asdk_action_hist.achi_id 
				where himo_field_modified='STATUS'
				and achi_item_id= @i_case_id --ID Caso
				)

				select @Tiempo2=SUM(DATEDIFF(minute,tmp.ini,tmp.fin))
				from(
					select A.himo_id,A.achi_item_id, A.himo_old_value, A.himo_new_value, serv_opened_date ini,ISNULL(B.achi_created,GETDATE()) fin
					from Tpe A
					left join Tpe B on A.ord = B.ord
					left join asdk_service_call on asdk_service_call.serv_id = A.achi_item_id
					)tmp
				WHERE tmp.himo_old_value=@i_status_id --ID Estado
				group by tmp.achi_item_id;


				IF(@Tiempo2 is null)
				BEGIN
					select @Tiempo2=DATEDIFF(minute,serv_opened_date,getdate())	from asdk_service_call where serv_id=@i_case_id;
				END
		
				set @Tiempo = @Tiempo2; 
			end

			--Incidentes
			ELSE IF(@Tiempo is null 
			and (select count(*) from afw_status_transition where sttr_code_fin=@i_status_id)=0
			and (select stat_app_category from afw_status where stat_id=@i_status_id )=1  )
			BEGIN
				
				with Tpe as( 
				select himo_id,achi_item_id, himo_old_value, himo_new_value, achi_created,ROW_NUMBER() OVER(ORDER BY himo_id) ord from asdk_hist_modify
				join asdk_action_hist on asdk_hist_modify.himo_action_hist_id = asdk_action_hist.achi_id 
				where himo_field_modified='STATUS'
				and achi_item_id= @i_case_id --ID Caso
				)

				select @Tiempo2=SUM(DATEDIFF(minute,tmp.ini,tmp.fin))
				from(
					select A.himo_id,A.achi_item_id, A.himo_old_value, A.himo_new_value, inci_opened_date ini,ISNULL(B.achi_created,GETDATE()) fin
					from Tpe A
					left join Tpe B on A.ord = B.ord
					left join asdk_incident on asdk_incident.inci_id = A.achi_item_id
					)tmp
				WHERE tmp.himo_old_value=@i_status_id --ID Estado
				group by tmp.achi_item_id;

				

				IF(@Tiempo2 is null)
				BEGIN
					select @Tiempo2=DATEDIFF(minute,inci_opened_date,getdate()) from asdk_incident where inci_id=@i_case_id;
				END
		
				set @Tiempo = @Tiempo2; 
			end

			--Cambios
			ELSE IF(@Tiempo is null 
			and (select count(*) from afw_status_transition where sttr_code_fin=@i_status_id)=0
			and (select stat_app_category from afw_status where stat_id=@i_status_id )=3  )
			BEGIN

				with Tpe as( 
				select himo_id,achi_item_id, himo_old_value, himo_new_value, achi_created,ROW_NUMBER() OVER(ORDER BY himo_id) ord from asdk_hist_modify
				join asdk_action_hist on asdk_hist_modify.himo_action_hist_id = asdk_action_hist.achi_id 
				where himo_field_modified='STATUS'
				and achi_item_id= @i_case_id --ID Caso
				)

				select @Tiempo2=SUM(DATEDIFF(minute,tmp.ini,tmp.fin))
				from(
					select A.himo_id,A.achi_item_id, A.himo_old_value, A.himo_new_value, chan_opened_date ini,ISNULL(B.achi_created,GETDATE()) fin
					from Tpe A
					left join Tpe B on A.ord = B.ord
					left join asdk_change on asdk_change.chan_id = A.achi_item_id
					)tmp
				WHERE tmp.himo_old_value=@i_status_id --ID Estado
				group by tmp.achi_item_id;

				

				IF(@Tiempo2 is null)
				BEGIN
					select @Tiempo2=DATEDIFF(minute,chan_opened_date,getdate()) from asdk_change where chan_id=@i_case_id;
				END

				set @Tiempo = @Tiempo2; 
			end
	END
	
	--Tiempo con calendario
	ELSE IF(@i_with_calendar=1)
	BEGIN
		with Tpe as( 
			select himo_id,achi_item_id, himo_old_value, himo_new_value, achi_created,ROW_NUMBER() OVER(ORDER BY himo_id) ord from asdk_hist_modify
			join asdk_action_hist on asdk_hist_modify.himo_action_hist_id = asdk_action_hist.achi_id 
			where himo_field_modified='STATUS'
			and achi_item_id= @i_case_id

			)

				select  @Tiempo=
				SUM( DISTINCT
				dbo.FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,
				tmp.ini,
				tmp.fin )

				)/60
				from(
					select A.himo_id,A.achi_item_id, A.himo_old_value, A.himo_new_value, A.achi_created ini,ISNULL(B.achi_created,GETDATE()) fin
					from Tpe A
					left join Tpe B on A.ord = B.ord-1
					)tmp
					left join asdk_service_call on asdk_service_call.serv_id = tmp.achi_item_id
					left join asdk_incident on asdk_incident.inci_id = tmp.achi_item_id
					left join asdk_change on asdk_change.chan_id = tmp.achi_item_id
					join asdk_service on asdk_service_call.serv_service_id = asdk_service.FL_INT_SERVICE_ID or asdk_incident.inci_service_id = asdk_service.FL_INT_SERVICE_ID or 
																			 asdk_change.chan_service_id = asdk_service.FL_INT_SERVICE_ID
					WHERE tmp.himo_new_value=@i_status_id
					group by tmp.achi_item_id;

			
			--Llamadas de servicio

			IF(@Tiempo is null 
			and (select count(*) from afw_status_transition where sttr_code_fin=@i_status_id)=0
			and (select stat_app_category from afw_status where stat_id=@i_status_id )=4  )
			BEGIN

				with Tpe as( 
				select himo_id,achi_item_id, himo_old_value, himo_new_value, achi_created,ROW_NUMBER() OVER(ORDER BY himo_id) ord from asdk_hist_modify
				join asdk_action_hist on asdk_hist_modify.himo_action_hist_id = asdk_action_hist.achi_id 
				where himo_field_modified='STATUS'
				and achi_item_id= @i_case_id --ID Caso
				)

				select @Tiempo2=dbo.FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,tmp.ini,tmp.fin)
				from(
					select A.himo_id,A.achi_item_id, A.himo_old_value, A.himo_new_value, serv_opened_date ini,ISNULL(B.achi_created,GETDATE()) fin
					from Tpe A
					left join Tpe B on A.ord = B.ord
					left join asdk_service_call on asdk_service_call.serv_id = A.achi_item_id
					)tmp
				left join asdk_service_call on asdk_service_call.serv_id = tmp.achi_item_id
				join asdk_service on  asdk_service_call.serv_service_id = asdk_service.FL_INT_SERVICE_ID
				WHERE tmp.himo_old_value=@i_status_id --ID Estado
				group by tmp.achi_item_id,asdk_service.fl_int_calendar_id,tmp.ini,tmp.fin;


				IF(@Tiempo2 is null)
				BEGIN
					select @Tiempo2= dbo.FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,serv_opened_date,getdate())	from asdk_service_call join asdk_service on  
								     asdk_service_call.serv_service_id = asdk_service.FL_INT_SERVICE_ID  where serv_id=@i_case_id;
				END
		
				set @Tiempo = @Tiempo2/60; 
			end


			-- Incidentes
			ELSE IF(@Tiempo is null 
			and (select count(*) from afw_status_transition where sttr_code_fin=@i_status_id)=0
			and (select stat_app_category from afw_status where stat_id=@i_status_id )=1  )
			BEGIN
				
				with Tpe as( 
				select himo_id,achi_item_id, himo_old_value, himo_new_value, achi_created,ROW_NUMBER() OVER(ORDER BY himo_id) ord from asdk_hist_modify
				join asdk_action_hist on asdk_hist_modify.himo_action_hist_id = asdk_action_hist.achi_id 
				where himo_field_modified='STATUS'
				and achi_item_id= @i_case_id --ID Caso
				)

				select @Tiempo2=dbo.FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,tmp.ini,tmp.fin)
				from(
					select A.himo_id,A.achi_item_id, A.himo_old_value, A.himo_new_value, inci_opened_date ini,ISNULL(B.achi_created,GETDATE()) fin
					from Tpe A
					left join Tpe B on A.ord = B.ord
					left join asdk_incident on asdk_incident.inci_id = A.achi_item_id
					)tmp
				left join asdk_incident on asdk_incident.inci_id = tmp.achi_item_id
				join asdk_service on  asdk_incident.inci_service_id = asdk_service.FL_INT_SERVICE_ID
				WHERE tmp.himo_old_value=@i_status_id --ID Estado
				group by tmp.achi_item_id,asdk_service.fl_int_calendar_id,tmp.ini,tmp.fin;


				IF(@Tiempo2 is null)
				BEGIN

					select @Tiempo2= dbo.FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,inci_opened_date,getdate()) from asdk_incident  join asdk_service on 
									 asdk_incident.inci_service_id = asdk_service.FL_INT_SERVICE_ID  where inci_id=@i_case_id;
				END
		
				set @Tiempo = @Tiempo2/60; 
			end

			-- Cambios
			ELSE IF(@Tiempo is null 
			and (select count(*) from afw_status_transition where sttr_code_fin=@i_status_id)=0
			and (select stat_app_category from afw_status where stat_id=@i_status_id )=3  )
			BEGIN

			with Tpe as( 
				select himo_id,achi_item_id, himo_old_value, himo_new_value, achi_created,ROW_NUMBER() OVER(ORDER BY himo_id) ord from asdk_hist_modify
				join asdk_action_hist on asdk_hist_modify.himo_action_hist_id = asdk_action_hist.achi_id 
				where himo_field_modified='STATUS'
				and achi_item_id= @i_case_id --ID Caso
				)

				select @Tiempo2=dbo.FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,tmp.ini,tmp.fin)
				from(
					select A.himo_id,A.achi_item_id, A.himo_old_value, A.himo_new_value, chan_opened_date ini,ISNULL(B.achi_created,GETDATE()) fin
					from Tpe A
					left join Tpe B on A.ord = B.ord
					left join asdk_change on asdk_change.chan_id = A.achi_item_id
					)tmp
				left join asdk_change on asdk_change.chan_id = tmp.achi_item_id
				join asdk_service on  asdk_change.chan_service_id = asdk_service.FL_INT_SERVICE_ID
				WHERE tmp.himo_old_value=@i_status_id --ID Estado
				group by tmp.achi_item_id,asdk_service.fl_int_calendar_id,tmp.ini,tmp.fin;


				IF(@Tiempo2 is null)
				BEGIN

					select @Tiempo2= dbo.FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,chan_opened_date,getdate()) from asdk_change  join asdk_service on 
									 asdk_change.chan_service_id = asdk_service.FL_INT_SERVICE_ID  where chan_id=@i_case_id;
				END
		
				set @Tiempo = @Tiempo2/60; 
			end
				
	END
	
	RETURN @Tiempo;
END