SET NOCOUNT ON

DECLARE @SessionID	NVARCHAR(36)
DECLARE @TaskID	NVARCHAR(36)

DECLARE curSDPurge CURSOR FOR
	SELECT Sessionid
	FROM ReportProcess
	WHERE ProjectName LIKE '%SD.%'
	
DECLARE curTaskPurge CURSOR FOR
	SELECT TaskID
	FROM Task
	WHERE UrlofProcess LIKE '%SD.%'
	OR [Name] IN ('Reopen Incident', 'Postponement')
	
OPEN curSDPurge
FETCH NEXT FROM curSDPurge INTO @SessionID

WHILE (@@FETCH_STATUS=0)
BEGIN	
	
	DELETE FROM ReportProcess
	WHERE SessionID = @SessionID
	
	DELETE FROM ReportProcessComment
	WHERE SessionID = @SessionID

	DELETE FROM ReportProcessContact
	WHERE SessionID = @SessionID
	
	DELETE FROM ReportProcessHistory
	WHERE SessionID = @SessionID
	
	DELETE FROM ReportProcessPermission
	WHERE SessionID = @SessionID
	
	DELETE FROM ReportProcessReference
	WHERE SessionID = @SessionID
	
	DELETE FROM ReportProcessStatusHistory
	WHERE SessionID = @SessionID
	
	DELETE FROM ReportProcessTiming
	WHERE SessionID = @SessionID
	
	FETCH NEXT FROM curSDPurge INTO @SessionID
END

OPEN curTaskPurge
FETCH NEXT FROM curTaskPurge INTO @TaskID

WHILE (@@FETCH_STATUS = 0)
BEGIN
	DELETE FROM TaskAssignment
	WHERE TaskID = @TaskID
	
	DELETE FROM TaskHistory
	WHERE TaskHistoryID = @TaskID
	
	DELETE FROM TaskResponse
	WHERE TaskID = @TaskID
	
	DELETE FROM Task
	WHERE TaskID = @TaskID

	FETCH NEXT FROM curTaskPurge INTO @TaskID
END

DELETE FROM ReportProcessRelationship
WHERE ParentProcessID LIKE 'IM-%'
OR ChildProcessID LIKE 'IM-%'

DELETE FROM ServiceDeskIncidentManagement
DELETE FROM ServiceDeskIncidentTemplate
DELETE FROM ServiceDeskOverAllSLASchedules
DELETE FROM ServiceDeskSLA
DELETE FROM ServiceDeskSLALevelManagement
DELETE FROM ServiceDeskSurveyData

DELETE FROM ReportProcessGenerator
WHERE [Type] LIKE '%M-%'
OR [Type] = 'SURVEY-'

DELETE FROM [Messages]
WHERE QueueName LIKE '%incident%'

CLOSE curSDPurge
DEALLOCATE curSDPurge

CLOSE curTaskPurge
DEALLOCATE curTaskPurge