ALTER VIEW [dbo].[vHdTicket] AS
SELECT
	liHdTicketId,
	nOpenTimeHours,
	nOpenTimeWithoutWaitingHours = nOpenTimeHours - (ISNULL(WaitedForUserHours,0)+ISNULL(nWaitingForUserHours,0)),
	nWaitedForUserHours = ISNULL(WaitedForUserHours,0)+ISNULL(nWaitingForUserHours,0),
	nWaitingForUserHours,
	NextAction,
	NextActionDeadline,	
	IsNextActionInternalTarget,
	SlaPause,
	IsSlaPaused
FROM (		
	SELECT
		liHdTicketId = tHdTicket.iHdTicketId,
		nOpenTimeHours = CASE WHEN SLA.liOpeningHoursId IS NULL 
							THEN CONVERT(float,DATEDIFF(hour, dHdTicket, COALESCE(dHdTicketResolved, GETUTCDATE())))
							ELSE (
								 SELECT nHdTicketDuration = COALESCE((SUM(CAST(OHT.dTo AS float) - CAST(OHT.dFrom AS float)) * 24), 0)
								 FROM (
										SELECT											
											dFrom = CASE WHEN dFrom < tHdTicket.dHdTicket THEN tHdTicket.dHdTicket ELSE dFrom END, 
											dTo = CASE WHEN dTo > (COALESCE(tHdTicket.dHdTicketResolved, GETUTCDATE())) THEN (COALESCE(tHdTicket.dHdTicketResolved, GETUTCDATE())) ELSE dTo END 
										FROM tOpeningHoursCache OH
										WHERE  
											OH.liOpeningHoursId = SLA.liOpeningHoursId 
											AND dTo > tHdTicket.dHdTicket 
											AND dFrom < (COALESCE(tHdTicket.dHdTicketResolved, GETUTCDATE())) 
										) OHT
								) END,
		WaitedForUserHours,
		nWaitingForUserHours = 
			CASE WHEN WaitingAct.iActId IS NOT NULL AND dHdTicketResolved IS NULL THEN
				ISNULL(
					(
						SELECT SUM(CAST(A.dTo - A.dFrom AS float))
						FROM (
							SELECT 
								CASE WHEN WaitingAct.dAct > dFrom THEN WaitingAct.dAct ELSE dFrom END dFrom,
								CASE WHEN dTo > ISNULL(tHdTicket.dHdTicketResolved,GETUTCDATE()) THEN ISNULL(tHdTicket.dHdTicketResolved,GETUTCDATE()) ELSE dTo END dTo
							FROM tOpeningHoursCache H
							WHERE
								H.liOpeningHoursId = SLA.liOpeningHoursId
								AND dTo > WaitingAct.dAct
								AND dFrom < ISNULL(tHdTicket.dHdTicketResolved,GETUTCDATE())
						) A
						WHERE dFrom < dTo
					) * 24.0,
					0
				)
			ELSE NULL
			END,
		NextAction = CASE
						WHEN (SLA.nSlaReactionHours IS NOT NULL) AND (tHdTicket.HasReaction = 0) AND (dHdTicketResolved IS NULL) THEN
				ReactionString.mString
						WHEN (dHdTicketDeadline IS NOT NULL) AND (dHdTicketResolved IS NULL) THEN
				ResolveString.mString
						ELSE
				NULL
			END,
		NextActionDeadline = tHdTicket.NextActionDeadline,
		IsNextActionInternalTarget = CASE WHEN tHdTicket.InternalTargetDeadline IS NULL 
											THEN 0
											ELSE CASE WHEN tHdTicket.NextActionDeadline IS NULL
														THEN 1
														ELSE CASE WHEN (tHdTicket.InternalTargetDeadline < tHdTicket.NextActionDeadline) THEN 1 ELSE 0 END
														END
										END,
		SlaPause = 
			CASE WHEN SLA.Pause = 1 
					AND dHdTicketDeadline = OriginalDeadline
				THEN 1
				ELSE 0
			END,
		IsSlaPaused = 
			CASE WHEN SLA.Pause = 1 
					AND WaitingAct.iActId IS NOT NULL
					AND dHdTicketDeadline = OriginalDeadline
					AND dHdTicketResolved IS NULL
				THEN 1
				ELSE 0
			END
	FROM tHdTicket
	INNER JOIN tSla SLA ON SLA.iSlaId = tHdTicket.liHdTicketSlaId
	LEFT JOIN tAct WaitingAct ON WaitingAct.iActId = tHdTicket.WaitingActId
	LEFT JOIN tString ReactionString ON ReactionString.iStringId = 47 	LEFT JOIN tString ResolveString ON ResolveString.iStringId = 48 ) t
GO

update T
set ShiftedDeadline = dbo.fDeadline(SLA.liOpeningHoursId, T.dHdTicket, SLA.nSlaDeadlineHours + isnull(T.WaitedForUserHours, 0))
from tHdTicket T
join tSla SLA on SLA.iSlaId = T.liHdTicketSlaId
where T.dHdTicketRemoved is null and T.dHdTicketResolved is not null and SLA.Pause = 1