DELETE FROM 
	dbo.PersonProgrammeActivity 
WHERE ProgrammeID = par_programmeid;

SELECT
	PP.PersonID,
	MIN(CASE WHEN PP.RegisterDate IS NULL THEN '9999-12-31' ELSE PP.RegisterDate END) RegisterDate,
	CAST(MAX(PP.SysEndTime) AS date) DeregisterDate
INTO t$php
FROM
(
	SELECT PersonID, RegisterDate, SysStartTime, SysEndTime	
	FROM dbo.PersonHasProgramme 
	WHERE ProgrammeID = par_programmeid
	UNION
	SELECT PersonID, RegisterDate, SysStartTime, SysEndTime	
	FROM dbo.PersonHasProgrammeHistory 
	WHERE ProgrammeID = par_programmeid
) PP
GROUP BY PP.PersonID

CREATE INDEX IX1 ON t$php(PersonID)

INSERT into t$php (PersonID, RegisterDate, DeregisterDate)
SELECT PersonID, FirstAppearDate, 
	CASE WHEN TRUNC(DATE_PART('day', NOW() - LastAppearDate)/7) > 1 THEN LastAppearDate ELSE '9999-12-31' END
FROM
(
	SELECT T.PersonID, MIN(T.Date) FirstAppearDate, 
		MAX(CASE WHEN T."teacher name" IS NULL THEN NULL ELSE T.Date END) LastAppearDate
	FROM {TABLE} T
	WHERE T.PersonID NOT IN (SELECT PersonID FROM t$php)
	GROUP BY T.PersonID
) X

MERGE PersonProgrammeActivity AS target
USING
(
	SELECT 
		P.PersonID,
		par_programmeid ProgrammeID,
		NULLIF(CASE WHEN P.RegisterDate IS NULL THEN CASE WHEN M.PersonID IS NOT NULL THEN M.FirstAppearDate ELSE A.FirstAppearDate END ELSE P.RegisterDate END,'9999-12-31') RegisterDate,
		CASE WHEN DATEDIFF(week,M.LastAppearDate,GETDATE()) >= 1 THEN M.LastAppearDate ELSE CAST(NULLIF(P.DeregisterDate,'9999-12-31') AS date) END DeregisterDate,
		CASE WHEN M.PersonID IS NOT NULL THEN M.FirstAppearDate   ELSE A.FirstAppearDate   END FirstAppearDate,
		CASE WHEN M.PersonID IS NOT NULL THEN M.LastAppearDate    ELSE A.LastAppearDate    END LastAppearDate,
		CASE WHEN M.PersonID IS NOT NULL THEN M.FirstMovementDate ELSE A.FirstMovementDate END FirstMovementDate,
		CASE WHEN M.PersonID IS NOT NULL THEN M.LastMovementDate  ELSE A.LastMovementDate  END LastMovementDate
	FROM t$php P
	LEFT JOIN
	(
		SELECT 
			D.PersonID, 
			CASE WHEN MIN(D.PrevDate) < MIN(D.Date) THEN MIN(D.PrevDate) ELSE MIN(D.Date) END FirstAppearDate,
			MAX(D.Date) LastAppearDate,
			MIN(CASE WHEN D.Movement > 0 THEN D.Date ELSE NULL END) FirstMovementDate,
			MAX(CASE WHEN D.Movement > 0 THEN D.Date ELSE NULL END) LastMovementDate
		FROM
		(
			SELECT
				D.Date,
				D1.Date PrevDate,
				D.PersonID, 
				{MOVEMENTCOL} Movement,
				{MOVEMENTTYPECOL} MovementType
			FROM {TABLE} D
			LEFT JOIN {TABLE} D1 ON D.PersonID = D1.PersonID AND D.Date = DATEADD(day,7,D1.Date)
		) D
		GROUP BY D.PersonID	
	) M ON P.PersonID = M.PersonID
	LEFT JOIN
	(
		SELECT 
			D.PersonID, 
			CASE WHEN MIN(D.PrevDate) < MIN(D.Date) THEN MIN(D.PrevDate) ELSE MIN(D.Date) END FirstAppearDate,
			MAX(D.Date) LastAppearDate,
			MIN(CASE WHEN D.Movement > 0 THEN D.Date ELSE NULL END) FirstMovementDate,
			MAX(CASE WHEN D.Movement > 0 THEN D.Date ELSE NULL END) LastMovementDate
		FROM
		(
			SELECT
				D.Date,
				D1.Date PrevDate,
				D.PersonID, 
				{MOVEMENTCOL} Movement,
				{MOVEMENTTYPECOL} MovementType
			FROM {TABLE}Archive D
			JOIN PersonHistory PH ON D.PersonID = PH.PersonID
			LEFT JOIN {TABLE}Archive D1 ON D.PersonID = D1.PersonID AND D.Date = DATEADD(day,7,D1.Date)
			WHERE PH.SysStartTime = PH.SysEndTime
				AND D.Date >= var_ToTValidFrom
		) D
		GROUP BY D.PersonID
	) A ON P.PersonID = A.PersonID
) AS source
ON target.PersonID = source.PersonID AND target.ProgrammeID = source.ProgrammeID
WHEN NOT MATCHED THEN
	INSERT (PersonID, ProgrammeID, FirstAppearDate, LastAppearDate, FirstMovementDate, LastMovementDate, RegisterDate, DeregisterDate)
	VALUES (PersonID, ProgrammeID, FirstAppearDate, LastAppearDate, FirstMovementDate, LastMovementDate, RegisterDate, DeregisterDate)
WHEN MATCHED THEN
	UPDATE SET
		FirstAppearDate   = source.FirstAppearDAte,
		LastAppearDate    = source.LastAppearDate,
		FirstMovementDate = source.FirstMovementDate,
		LastMovementDate  = source.LastMovementDate,
		RegisterDate      = source.RegisterDate,
		DeregisterDate    = source.DeregisterDate;

-- RAISERROR(''Rows affected: %d'', 0, 0, @@ROWCOUNT) WITH NOWAIT;