Lag Function - Alternative

Transact-SQL
,
2

Before the LAG function, we had to rely on self joins and the ROW_NUMBER() function. See below...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NULL
BEGIN	-- DROP TABLE #TestData
	CREATE TABLE #TestData (
		ID INT NOT NULL,
		CreatedDate DATETIME NOT NULL,
		CompletedDate DATETIME NOT NULL,
		PRIMARY KEY CLUSTERED (ID, CreatedDate) WITH (IGNORE_DUP_KEY = ON)
		);
	
	INSERT #TestData (ID, createdDate, CompletedDate)
	SELECT 
		i.ID,
		cd1.CreatedDate,
		cd2.CompleteddDate
	FROM 
		dbo.tfn_Tally(1000, 1) t
		CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 15 + 1) ) i (ID)
		CROSS APPLY ( VALUES (DATEADD(hh, ABS(CHECKSUM(NEWID())) % 55555, '2015-01-15')) ) cd1 (CreatedDate)
		CROSS APPLY ( VALUES (DATEADD(hh, ABS(CHECKSUM(NEWID())) % 240 + 1, cd1.CreatedDate)) ) cd2 (CompleteddDate);
END;

--===========================================================================


WITH 
	cte_AddRN AS (
		SELECT 
			td.ID,
			td.CreatedDate,
			td.CompletedDate,
			RN = ROW_NUMBER() OVER (PARTITION BY td.ID ORDER BY td.CreatedDate) 
		FROM 
			#TestData td
		)
SELECT 
	arn1.ID,
	arn1.CreatedDate,
	arn1.CompletedDate,
	LaG_CompDate = arn2.CompletedDate,
	Delta = ABS(DATEDIFF(WEEK,arn1.CreatedDate, arn2.CompletedDate))  
FROM 
	cte_AddRN arn1
	LEFT JOIN cte_AddRN arn2
		ON arn1.ID = arn2.ID
		AND arn1.RN = arn2.RN + 1	-- +1 for LAG & -1 for LEAD
ORDER BY 
	arn1.id,
	arn1.CreatedDate;
1 Like