Originally posted by chrisrlong
Okay, we'll go with the easiest solution first.

If you store (in another table), the last submission that was applied to the results table, then this becomes much easier. Another useful component would be a DateDimension_T table. I almost always make one of these. It holds a record for every date (going bacwards and forwards a decade or so usually). There are many advantages to such a table, including being able to easily identify working days, holidays, which days belong to which quarters, etc. In this case, the primary advantage is the ability to convert ranges into rows. So, assuming we have the PrevAppliedSubmissionID somewhere that we can load into a variable (I usually have a GlobalValues_T table for such things), we would do:
Code:
UPDATE
	Results_T	R
SET
	(
	MainTableID		,
	Date				,
	QTY				,
	LastUserID		,
	LastTimestamp
	)	=
	(
	SELECT
		MainTableID		,
		SQL_Date			,
		QTY				,
		LastUserID		,
		LastTimestamp
	FROM
		(
		SELECT
			MT.MainTableID		,
			DD.SQL_Date			,
			MT.QTY				,
			MT.LastUserID		,
			MT.LastTimestamp	,
			ROW_NUMBER()
				OVER (
					PARTITION BY
						DD.DateDimension_ID
					ORDER BY
						MT.LastTimestamp DESC
					)
				AS RN
		FROM
			MainTable_T			MT	,
			DateDimension_T	DD
		WHERE
			MT.Submission_ID	>			:Submission_ID	AND
			DD.SQL_DT			BETWEEN	MT.SDate
									AND		MT.EDate
		)
	WHERE
		RN	=	1
	)
WHERE
	MT.Submission_ID	>			:Submission_ID


INSERT INTO
	Results_T	R
SELECT
	MainTableID		,
	QTY				,
	LastUserID		,
	LastTimestamp
FROM
	(
	SELECT
		MainTableID		,
		QTY				,
		LastUserID		,
		LastTimestamp	,
		ROW_NUMBER()
			OVER (
				PARTITION BY
					DD.DateDimension_ID
				ORDER BY
					MT.LastTimestamp DESC
				)
			AS RN
	FROM
		MainTable_T			MT	,
		DateDimension_T	DD
	WHERE
		MT.Submission_ID	>			:Submission_ID	AND
		DD.SQL_DT			BETWEEN	MT.SDate
								AND		MT.EDate
	)	T
WHERE
	RN	=	1	AND
	NOT EXISTS	(
		SELECT
			1
		FROM
			Results_T	R2
		WHERE
			R2.Date	=	T.Date
		)
So now we have the results table populated. Oh, and don't forget to update the LastSubmissionID value within the same transaction.

Now, we need to convert these values back into ranges for the output. Give me a minute and I'll craft that for ya.


- Chris
Geez, That's the EASY solution?