Using a PARTITION BY JOIN to Fill In The Gaps in Sparse Data

Transact-SQL
4

Depending on you data distribution, CROSS APPLY might make a difference:

SELECT V.TABLE_NAME 
	,V.PROGRAM_NO 
	,V.COLUMN_NAME 
	,A.CREATION_TIMESTAMP 
	,A.RECORD_KEY 
	,A.BEFORE_VALUE 
	,A.AFTER_VALUE 
FROM AUDIT_010_V V
	CROSS APPLY
	(
		SELECT DISTINCT TABLE_NAME, CREATION_TIMESTAMP, RECORD_KEY
		FROM AUDIT_DATA D
		WHERE D.TABLE_NAME = V.TABLE_NAME
	) P
	LEFT JOIN AUDIT_DATA A
		ON V.TABLE_NAME = A.TABLE_NAME
			AND V.COLUMN_NAME = A.COLUMN_NAME
			AND P.CREATION_TIMESTAMP = A.CREATION_TIMESTAMP
			AND P.RECORD_KEY = A.RECORD_KEY
ORDER BY TABLE_NAME, COLUMN_NAME, CREATION_TIMESTAMP, RECORD_KEY;
1 Like