alter procedure [dbo].[spUpdateSlaPausedHours] (@ticketId int) as 
begin 
    -- Datum první reakce
	declare @firstReactionDate datetime
	SELECT top 1 @firstReactionDate = dAct
	FROM tAct ReactionAct
	LEFT JOIN tHdTicket T ON T.iHdTicketId = ReactionAct.liActHdTicketId
	WHERE ((ReactionAct.iActId != T.liHdTicketStartingActId
			AND ReactionAct.liActKindId NOT IN (8, 11)
			AND ((
				ReactionAct.liActKindId != 9 AND
				(ReactionAct.liActToPersonId = T.liHdTicketUserPersonId 
				OR ReactionAct.sActToEmail = T.sHdTicketUserEmail OR ReactionAct.sActToEmail = T.UserEmail2 
				OR ReactionAct.sActTo LIKE ('%' + T.sHdTicketUserEmail + '%') OR ReactionAct.sActTo LIKE ('%' + T.UserEmail2 + '%') 
				OR ReactionAct.sActCc LIKE ('%' + T.sHdTicketUserEmail + '%') OR ReactionAct.sActCc LIKE ('%' + T.UserEmail2 + '%'))
				) OR (ReactionAct.liActKindId = 9 AND ReactionAct.ActOperationId IN (3,11))
					OR (ReactionAct.liActKindId = 9 AND ReactionAct.ActOperationId = 2 AND T.liHdTicketSolverPersonId = T.liHdTicketUserPersonId))
		    ) 
			OR (ReactionAct.iActId = T.liHdTicketStartingActId 
			    AND ReactionAct.liActKindId IN (2, 3) 
				AND ReactionAct.liActToPersonId = T.liHdTicketUserPersonId)
		    ) 
			AND ReactionAct.liActFromPersonId IS NOT NULL
			AND ReactionAct.dActRemoved IS NULL
			AND ReactionAct.liActHdTicketId = @ticketId
			AND (ReactionAct.liActFromPersonId = T.liHdTicketSolverPersonId OR ReactionAct.liActFromPersonId IN 
					(
						SELECT PersonId FROM HdSectionSolver SS
						WHERE T.liHdTicketHdSectionId = SS.HdSectionId
						UNION 
						SELECT PersonId FROM HdSectionOperator SO
						WHERE T.liHdTicketHdSectionId = SO.HdSectionId
						UNION 
						SELECT PersonId FROM HdSectionManager SM
						WHERE T.liHdTicketHdSectionId = SM.HdSectionId
					)
				 OR ReactionAct.liActFromPersonId = 1
				)
	ORDER BY dAct asc

	-- Datum začátku aktuálního pozastavení SLA
	declare @currentPauseStart datetime = null
	select @currentPauseStart = min(currentPauseStart) 
	from (
		select top 1 (case when PauseStart = 1 then ChangeDate else null end) currentPauseStart
		from (
			select PauseStart, PauseStop, ChangeDate = MIN([Date])
			from (
				select *, RN = ROW_NUMBER() OVER (ORDER BY [Date]) - ROW_NUMBER() OVER (PARTITION BY PauseStart, PauseStop ORDER BY [Date])
				from (
					select * from
					(										    
						-- Události s příznakem čekání na žadatele
						select dAct [Date], bWaitingForUser as PauseStart,  bWaitingForUser^1 as PauseStop
						from tAct a
						join tHdTicket t on t.iHdTicketId=a.liActHdTicketId and t.iHdTicketId = @ticketId
						join tSla curSla on curSla.iSlaId = t.liHdTicketSlaId
						where bWaitingForUser is not null and curSla.[Pause] = 1 											
					) as PauseEvents
					where PauseEvents.PauseStart != PauseEvents.PauseStop
				) AS siEvent
			) AS siRnEvent
			group by PauseStart, PauseStop, RN
		) as siEnd 
		order by ChangeDate desc

		union all

		select top 1 (case when PauseStart = 1 then ChangeDate else null end) currentPauseStart
		from (
			select PauseStart, PauseStop, ChangeDate = MIN([Date])
			from (
				select *, RN = ROW_NUMBER() OVER (ORDER BY [Date]) - ROW_NUMBER() OVER (PARTITION BY PauseStart, PauseStop ORDER BY [Date])
				from (
					-- Tabulka událostí, které spustily nebo pozastavily SLA
					select * from (
						-- Změny stavů
						select tc.ChangeDate [Date], case when tsTo.id is null then 0 else 1 end as PauseStart, case when tsFrom.id is null then 0 else 1 end as PauseStop
						from tColumn c
						join TicketChange tc on c.iColumnId = tc.ColumnId
						join tHdTicket t on tc.TicketId=t.iHdTicketId and t.iHdTicketId = @ticketId
						join tHdSection s on t.liHdTicketHdSectionId=s.iHdSectionId
						join TicketType tt on s.TicketTypeId=tt.id
						left join TicketState tsTo on (case when c.sColumn='TicketStateId' then cast(tc.ValueNew as int) else 0 end)=tsTo.id and tsTo.PauseSla=1 and tsTo.TicketTypeId=tt.id
						left join TicketState tsFrom on (case when c.sColumn='TicketStateId' then cast(tc.ValueOld as int) else 0 end)=tsFrom.id and tsFrom.PauseSla=1 and tsFrom.TicketTypeId=tt.id
						where c.sTable='tHdTicket' and c.sColumn='TicketStateId'										
		
						union all
					
						-- Přidání prvního stavu požadavku
						select dHdTicket, case when ts.PauseSla=1 then 1 else 0 end, case when ts.PauseSla=1 then 0 else 1 end
						from tHdTicket t 
						join tHdSection s on t.liHdTicketHdSectionId=s.iHdSectionId 
						join TicketType tt on s.TicketTypeId=tt.id 
						join TicketState ts on tt.id=ts.TicketTypeId and ts.TicketStateBehaviorId=1
						where t.iHdTicketId = @ticketId
					) PauseEventsState
					where PauseEventsState.PauseStart != PauseEventsState.PauseStop 
				) AS siState
			) AS siRnState
			group by PauseStart, PauseStop, RN
		) as siStateEnd 
		order by ChangeDate desc
	) as curPauseStart

	declare @totalHoursToFirstReaction float = 0, @totalHoursInOpeningHours float = 0

	select @totalHoursInOpeningHours = sum(slaPaused), -- Celková doba pozastavení SLA
		   @totalHoursToFirstReaction = sum(case when paused.EndDate <= @firstReactionDate then slaPaused else 0 end) -- Celková doba pozastavení SLA do první reakce
	from (
		select change.StartDate, 
			   change.EndDate, 
			   slaPaused = ISNULL(
				(
					-- Výpočet doby pozastavení na jednotlivých intervalech SLA
					SELECT SUM(CAST(datediff(minute, W.dFrom, W.dTo)*1.0/60*1.0 AS float))
					FROM (
						SELECT 
							case when SI.StartDate < OH.dFrom then OH.dFrom else SI.StartDate end as dFrom, 
							case when SI.EndDate > OH.dTo then OH.dTo else SI.EndDate end as dTo
						FROM (
							SELECT 
								CASE WHEN change.StartDate > H.dFrom THEN change.StartDate ELSE H.dFrom END dFrom,
								CASE WHEN H.dTo >  isnull(change.EndDate, getutcdate()) THEN  isnull(change.EndDate, getutcdate()) ELSE H.dTo END dTo
							FROM tOpeningHoursCache H
							WHERE
								H.liOpeningHoursId = tSla.liOpeningHoursId
								AND H.dTo > change.StartDate
								AND H.dFrom < isnull(change.EndDate, getutcdate())
						) OH
						JOIN (
						    -- Tabulka událostí, které spustily nebo pozastavily SLA
							select PauseStart, PauseStop, StartDate, LEAD(StartDate, 1, isnull(@currentPauseStart, GETUTCDATE())) OVER (ORDER BY StartDate) AS EndDate
							from (
							    -- Pokud následují za sebou dvě události různých typů (změna stavu a čekání na žadatele), které pozastavují SLA vybere se ta dřívější.
								-- Pokud následují za sebou dvě události různých typů (změna stavu a čekání na žadatele), které spouští SLA vybere se ta pozdější.
								select PauseStart, PauseStop, StartDate = case when PauseStart = 1 then MIN(ChangeDate) else MAX(ChangeDate) end
								from (
									select *, RN = ROW_NUMBER() OVER (ORDER BY ChangeDate) - ROW_NUMBER() OVER (PARTITION BY PauseStart, PauseStop ORDER BY ChangeDate)
									from (
										-- Pokud je za sebou více událostí změny stavu, které spouští/zastavují SLA je vybrána ta dřívější
										select PauseStart, PauseStop, ChangeDate = MIN([Date])
										from (
											select *, RN = ROW_NUMBER() OVER (ORDER BY [Date]) - ROW_NUMBER() OVER (PARTITION BY PauseStart, PauseStop ORDER BY [Date])
											from (
												select * from (
													-- Změny stavů
													select tc.ChangeDate [Date], case when tsTo.id is null then 0 else 1 end as PauseStart, case when tsFrom.id is null then 0 else 1 end as PauseStop
													from tColumn c
													join TicketChange tc on c.iColumnId = tc.ColumnId
													join tHdTicket t on tc.TicketId=t.iHdTicketId and t.iHdTicketId = change.iHdTicketId
													join tHdSection s on t.liHdTicketHdSectionId=s.iHdSectionId
													join TicketType tt on s.TicketTypeId=tt.id
													left join TicketState tsTo on (case when c.sColumn='TicketStateId' then cast(tc.ValueNew as int) else 0 end)=tsTo.id and tsTo.PauseSla=1 and tsTo.TicketTypeId=tt.id
													left join TicketState tsFrom on (case when c.sColumn='TicketStateId' then cast(tc.ValueOld as int) else 0 end)=tsFrom.id and tsFrom.PauseSla=1 and tsFrom.TicketTypeId=tt.id
													where c.sTable='tHdTicket' and c.sColumn='TicketStateId'										
		
													union all
					
													-- Přidání prvního stavu požadavku
													select dHdTicket, case when ts.PauseSla=1 then 1 else 0 end, case when ts.PauseSla=1 then 0 else 1 end
													from tHdTicket t 
													join tHdSection s on t.liHdTicketHdSectionId=s.iHdSectionId 
													join TicketType tt on s.TicketTypeId=tt.id 
													join TicketState ts on tt.id=ts.TicketTypeId and ts.TicketStateBehaviorId=1
													where t.iHdTicketId = change.iHdTicketId
												) PauseEventsState
												where PauseEventsState.PauseStart != PauseEventsState.PauseStop and (@currentPauseStart is null or PauseEventsState.[Date] < @currentPauseStart)
											) AS siState
										) AS siRnState
										GROUP BY PauseStart, PauseStop, RN

										union all

										-- Pokud je za sebou více událostí čekání na žadatele, které spouští/zastavují SLA je vybrána ta dřívější
										select PauseStart, PauseStop, ChangeDate = MIN([Date])
										from (
											select *, RN = ROW_NUMBER() OVER (ORDER BY [Date]) - ROW_NUMBER() OVER (PARTITION BY PauseStart, PauseStop ORDER BY [Date])
											from (
												select * from
												(										    
													-- Události s příznakem čekání na žadatele
													select dAct [Date], bWaitingForUser as PauseStart,  bWaitingForUser^1 as PauseStop
													from tAct a
													join tHdTicket t on t.iHdTicketId=a.liActHdTicketId and t.iHdTicketId = change.iHdTicketId
													join tSla slaInterval on slaInterval.iSlaId = change.liHdTicketSlaId
													where bWaitingForUser is not null and slaInterval.[Pause] = 1 											
												) as PauseEvents
												where PauseEvents.PauseStart != PauseEvents.PauseStop and (@currentPauseStart is null or PauseEvents.[Date] < @currentPauseStart)
											) AS siEvent
										) AS siRnEvent
										GROUP BY PauseStart, PauseStop, RN
									) as si
								) siRn
								GROUP BY PauseStart, PauseStop, RN
							) as siEnd
						) SI ON SI.PauseStart = 1 and SI.EndDate > OH.dFrom and SI.StartDate < OH.dTo
					) W
				)*1.0, 0)
		from (
			-- Tabulka změn SLA a interval jejich trvání
			select subchange.*,
					LEAD(subchange.StartDate, 1, NULL) OVER (ORDER BY subchange.StartDate) AS EndDate
			from (
				-- Všechny změny na nové SLA
				select 
					t.iHdTicketId,
					case when c.sColumn = 'liHdTicketSlaId' then cast(tc.ValueNew as int)
						else ISNULL((select top 1 cast(tcSla.ValueOld as int) actSlaId
									from tColumn cSla 
									join TicketChange tcSla on tcSla.ColumnId = cSla.iColumnId
									where tcSla.TicketId = t.iHdTicketId and tcSla.ChangeDate > tc.ChangeDate and cSla.sTable = 'tHdTicket' and cSla.sColumn = 'liHdTicketSlaId'
									order by tcSla.ChangeDate asc), t.liHdTicketSlaId) 
						end as liHdTicketSlaId,
					tc.ChangeDate as StartDate
				from TicketChange tc
				join tHdTicket t on t.iHdTicketId = tc.TicketId and t.iHdTicketId = @ticketId
				join tColumn c on c.iColumnId = tc.ColumnId and c.sTable = 'tHdTicket' and c.sColumn in ('liHdTicketSlaId')
		
				union all

				-- Přidání prvního SLA na požadavku
				select t.iHdTicketId,			
					ISNULL((select top 1 cast(tcSla.ValueOld as int) actSlaId
							from tColumn cSla
							join TicketChange tcSla on tcSla.ColumnId = cSla.iColumnId
							where tcSla.TicketId = t.iHdTicketId and cSla.sTable = 'tHdTicket' and cSla.sColumn = 'liHdTicketSlaId'
							order by tcSla.ChangeDate asc), t.liHdTicketSlaId) 
						as liHdTicketSlaId,
					t.dHdTicket
				from tHdTicket t
				where t.iHdTicketId = @ticketId

				union all

				-- Rozdělení jednoho z intervalů datumem první reakce (kvůli výpočtu pozastavení SLA do první reakce)
				select t.iHdTicketId,
						ISNULL(ISNULL((select top 1 cast(tcSla.ValueNew as int) actSlaId
										from tColumn cSla
										join TicketChange tcSla on tcSla.ColumnId = cSla.iColumnId
										where tcSla.TicketId = t.iHdTicketId and cSla.sTable = 'tHdTicket' and cSla.sColumn = 'liHdTicketSlaId' and tcSla.ChangeDate < @firstReactionDate
										order by tcSla.ChangeDate desc),
										(select top 1 cast(tcSla.ValueOld as int) actSlaId
										from tColumn cSla
										join TicketChange tcSla on tcSla.ColumnId = cSla.iColumnId
										where tcSla.TicketId = t.iHdTicketId and cSla.sTable = 'tHdTicket' and cSla.sColumn = 'liHdTicketSlaId'
										order by tcSla.ChangeDate asc)),
								t.liHdTicketSlaId) as liHdTicketSlaId,
						@firstReactionDate 
				from tHdTicket t
				where t.iHdTicketId = @ticketId
			) as subchange
		) as change
		join tSla on iSlaId = change.liHdTicketSlaId
	) as paused

	-- Update tHdTicket
	update t 
	set SlaPausedHours = @totalHoursInOpeningHours,
		ShiftedFirstReactionDeadline = (CASE WHEN v.SlaPause=1 THEN dbo.fDeadline(s.liOpeningHoursId, t.dHdTicket, s.nSlaReactionHours + isnull(case when @firstReactionDate is null then @totalHoursInOpeningHours else @totalHoursToFirstReaction end, 0)) ELSE ShiftedFirstReactionDeadline END),
		ShiftedDeadline = (CASE WHEN v.SlaPause=1 THEN dbo.fDeadline(s.liOpeningHoursId, t.dHdTicket, s.nSlaDeadlineHours + isnull(@totalHoursInOpeningHours, 0)) END),
		CurrentSlaPauseStart = DATEADD(ss,-datepart(ss, @currentPauseStart), @currentPauseStart)
	from tHdTicket t 
	join vHdTicket v on t.iHdTicketId = v.liHdTicketId 
	join tSla s on t.liHdTicketSlaId = s.iSlaId
	left join tAct waitingForRequester on waitingForRequester.iActId = t.WaitingActId
	left join TicketState currentState on currentState.id=t.TicketStateId
	where iHdTicketId=@ticketId
end