
CREATE TABLE #ticketsToUpdate (id int PRIMARY KEY, processed int)

INSERT INTO #ticketsToUpdate
    SELECT iHdTicketId, 0
	FROM tHdTicket 
	WHERE dHdTicket < '2025-11-06'
		AND ClosedDate IS NULL

WHILE (EXISTS(SELECT 1 FROM #ticketsToUpdate WHERE processed = 0))
BEGIN
	DECLARE @id int;
	SELECT @id = id FROM #ticketsToUpdate WHERE processed = 0

	UPDATE dbo.tHdTicket SET WaitedForUserHours =
	(
	SELECT SUM(CAST(B.dTo - B.dFrom AS float))
	FROM (
		SELECT 
			A.liActHdTicketId,
			CASE WHEN A.dFrom > OHC.dFrom THEN A.dFrom ELSE OHC.dFrom END dFrom,
			CASE WHEN A.dTo < OHC.dTo THEN A.dTo ELSE OHC.dTo END dTo
		FROM
			(
				SELECT
					A1.liActHdTicketId,
					A1.dAct AS dFrom,
					ISNULL(A2.dAct,GETUTCDATE()) AS dTo
				FROM tAct A1
				INNER JOIN tAct A2 ON A2.liActHdTicketId=A1.liActHdTicketId
					AND A2.bWaitingForUser IS NOT NULL
					AND (A2.dAct>A1.dAct OR A2.dAct=A1.dAct AND A2.iActId>A1.iActId)
					AND NOT EXISTS (
						SELECT 1
						FROM tAct A3
						WHERE A3.liActHdTicketId=A1.liActHdTicketId
							AND A3.bWaitingForUser IS NOT NULL
							AND (A3.dAct>A1.dAct OR A3.dAct=A1.dAct AND A3.iActId>A1.iActId)
							AND (A3.dAct<A2.dAct OR A3.dAct=A2.dAct AND A3.iActId<A2.iActId)
							AND A3.dActRemoved IS NULL
					)
				WHERE A1.bWaitingForUser=1
					AND A1.dActRemoved IS NULL
					AND A2.dActRemoved IS NULL
			) A
			INNER JOIN tSla SLA ON SLA.iSlaId = tHdTicket.liHdTicketSlaId
			INNER JOIN dbo.tOpeningHoursCache OHC ON OHC.liOpeningHoursId=SLA.liOpeningHoursId
				AND OHC.dFrom<A.dTo
				AND OHC.dTo>A.dFrom
			) B				
			WHERE B.liActHdTicketId=@id
	) * 24.0
	WHERE iHdTicketId = @id

	UPDATE #ticketsToUpdate SET processed = 1 WHERE id = @id
END

DROP TABLE #ticketsToUpdate