DECLARE
	@ArchiveDate nvarchar(14),
	@ArchiveForumID int

SET
	@ArchiveDate = '20030701000000'  -- Date to archive from (YYYYMMDDHHMMSS)
SET
	@ArchiveForumID = 35  -- Forum ID to Archive


-- Create temp table that will hold TOPIC_ID's to archive
CREATE TABLE #TOPIC_IDs
(
	TOPIC_ID int
)

-- Fill temp table with TOPIC_ID's to archive
INSERT INTO
	#TOPIC_IDs
-- Query finds topics started (and edited) before archive date
SELECT
	TOPIC_ID
FROM
	FORUM_TOPICS AS FT
WHERE
	T_DATE < @ArchiveDate
AND
	-- If no last edit date (NULL), return lowest date possible
	ISNULL(T_LAST_EDIT,'00000000000000') < @ArchiveDate
	
AND
	FORUM_ID = @ArchiveForumID
AND
	-- Subquery to determine if any replies (or edits) are 
	--  AFTER the archive date.  If so, do not archive topic.
	(SELECT
		COUNT(*)
	FROM
		FORUM_REPLY AS FR
	WHERE
		(R_DATE >= @ArchiveDate
		OR
		-- If no last edit date (NULL), return lowest date possible
		ISNULL(R_LAST_EDIT,'00000000000000') >= @ArchiveDate)
	AND
		FR.TOPIC_ID = FT.TOPIC_ID
	) = 0


-- Copy TOPIC's to archive table
INSERT INTO
	FORUM_A_TOPICS
	(CAT_ID,
	FORUM_ID,
	TOPIC_ID,
	T_STATUS,
	T_MAIL,
	T_SUBJECT,
	T_MESSAGE,
	T_AUTHOR,
	T_REPLIES,
	T_UREPLIES,
	T_VIEW_COUNT,
	T_LAST_POST,
	T_DATE,
	T_LAST_POSTER,
	T_IP,
	T_LAST_POST_AUTHOR,
	T_LAST_POST_REPLY_ID,
	T_ARCHIVE_FLAG,
	T_LAST_EDIT,
	T_LAST_EDITBY,
	T_STICKY,
	T_SIG,
	T_EVENT_DATE,
	T_ISEVENT)
SELECT
	CAT_ID,
	FORUM_ID,
	TOPIC_ID,
	0,
	T_MAIL,
	T_SUBJECT,
	T_MESSAGE,
	T_AUTHOR,
	T_REPLIES,
	T_UREPLIES,
	T_VIEW_COUNT,
	T_LAST_POST,
	T_DATE,
	T_LAST_POSTER,
	T_IP,
	T_LAST_POST_AUTHOR,
	T_LAST_POST_REPLY_ID,
	T_ARCHIVE_FLAG,
	T_LAST_EDIT,
	T_LAST_EDITBY,
	T_STICKY,
	T_SIG,
	T_EVENT_DATE,
	T_ISEVENT
FROM
	FORUM_TOPICS
WHERE
	TOPIC_ID
	IN
	(SELECT
		TOPIC_ID
	FROM
		#TOPIC_IDs)


-- Copy REPLY's to archive table
INSERT INTO
	FORUM_A_REPLY
	(CAT_ID,
	FORUM_ID,
	TOPIC_ID,
	REPLY_ID,
	R_MAIL,
	R_AUTHOR,
	R_MESSAGE,
	R_DATE,
	R_IP,
	R_STATUS,
	R_LAST_EDIT,
	R_LAST_EDITBY,
	R_SIG)
SELECT
	CAT_ID,
	FORUM_ID,
	TOPIC_ID,
	REPLY_ID,
	R_MAIL,
	R_AUTHOR,
	R_MESSAGE,
	R_DATE,
	R_IP,
	R_STATUS,
	R_LAST_EDIT,
	R_LAST_EDITBY,
	R_SIG
FROM
	FORUM_REPLY
WHERE
	TOPIC_ID
	IN
	(SELECT
		TOPIC_ID
	FROM
		#TOPIC_IDs)

-- Delete TOPIC's from main table
DELETE
FROM
	FORUM_TOPICS
WHERE
	TOPIC_ID
	IN
	(SELECT
		TOPIC_ID
	FROM
		#TOPIC_IDs)

-- Delete REPLY's from main table
DELETE
FROM
	FORUM_REPLY
WHERE
	TOPIC_ID
	IN
	(SELECT
		TOPIC_ID
	FROM
		#TOPIC_IDs)

DROP TABLE #TOPIC_IDs

-- Update FORUM_TOTALS Counts
UPDATE
	FORUM_TOTALS
SET
	P_COUNT = (
		SELECT
			COUNT(*)
		FROM
			FORUM_REPLY
		),
	P_A_COUNT = (
		SELECT
			COUNT(*)
		FROM
			FORUM_A_REPLY
		),
	T_COUNT = (
		SELECT
			COUNT(*)
		FROM
			FORUM_TOPICS
		),
	T_A_COUNT = (
		SELECT
			COUNT(*)
		FROM
			FORUM_A_TOPICS
		)

-- Update Specific Forum Counts
UPDATE
	FORUM_FORUM
SET
	F_TOPICS = (
		SELECT
			COUNT(*)
		FROM
			FORUM_TOPICS
		WHERE
			FORUM_ID = @ArchiveForumID
		),
	F_COUNT = (
		SELECT
			COUNT(*)
		FROM
			FORUM_REPLY
		WHERE
			FORUM_ID = @ArchiveForumID
		),
	F_A_TOPICS = (
		SELECT
			COUNT(*)
		FROM
			FORUM_A_TOPICS
		WHERE
			FORUM_ID = @ArchiveForumID
		),
	F_A_COUNT = (
		SELECT
			COUNT(*)
		FROM
			FORUM_A_REPLY
		WHERE
			FORUM_ID = @ArchiveForumID
		)
WHERE
	FORUM_ID = @ArchiveForumID
								

GO