Alternative for LEAD function

Transact-SQL
,
1

Hi,

I have a query which is working when I use the server1 but when I run the script from prod sever the query is not working and got an error say LEAD function is ????...
Is there any alternative approach? below is DDL and sample data.

DECLARE	@Prod TABLE
	(
		pd nvarchar(50),
		id nvarchar(50),
		serial nvarchar(50),
		prod_dt datetime,
		line nvarchar(25),
		pstatus nvarchar(25)
	)

-- Populate sample data
INSERT	@Prod VALUES	('T000012','000000000000056','30045','2017-9-25 10:11:55.000','Line1','Created Order')
INSERT	@Prod VALUES	('T000012','000000000000056','30045','2017-10-15 16:12:31.000','Line1','Order Complete')

INSERT	@Prod VALUES	('T000047','000000000000056','NULL','2017-11-30 11:11:55.000','Line1','Created Order')
INSERT	@Prod VALUES	('T000047','000000000000056','NULL','2017-11-30 12:12:31.000','Line1','Order Complete')

INSERT	@Prod VALUES	('T000020','000000000000056','30045','2017-12-01 08:11:02.000','LineT','Created Order')
INSERT	@Prod VALUES	('T000020','000000000000056','30045','2017-12-05 17:03:58.000','LineT','Order Complete')

INSERT	@Prod VALUES	('T000018','000000000000014','30061','2017-12-01 08:00:56.000','Line2','Created Order')
INSERT	@Prod VALUES	('T000018','000000000000014','30061','2017-12-06 09:10:46.000','Line2','Order Complete')
INSERT	@Prod VALUES	('T000014','000000000000022','30022','2017-12-01 08:41:11.000','Line3','Created Order')


INSERT	@Prod VALUES	('865298','000000000000052','3384','2017-12-10 16:53:17.000','1STr','Order Complete')
INSERT	@Prod VALUES	('865298','000000000000052','3384','2017-12-11 04:47:52.000','1STr','Created Order')
INSERT	@Prod VALUES	('947526','000000000000052','3384','2017-12-21 13:15:18.000','1STr','Order Complete')
INSERT	@Prod VALUES	('947526','000000000000052','3384','2017-12-23 21:47:47.000','1STr','Created Order')

INSERT	@Prod VALUES	('T000015','000000000000400','3382','2017-12-09 02:40:51.000','1STr','Created Order')
INSERT	@Prod VALUES	('T000015','000000000000400','3382','2017-12-09 08:37:24.000','1STr','Order Complete')
INSERT	@Prod VALUES	('T000016','000000000000400','3382','2017-12-21 13:49:15.000','1STr','Created Order')



DECLARE	@Receipt TABLE
	(
		id nvarchar(50),
		itemdesc nvarchar(35),
		receipt_dt datetime,
		sourceid int
	)

-- Populate sample data
INSERT	@Receipt VALUES	('000000000000056','AAAA','2017-8-10 10:10:20.000',3)
INSERT	@Receipt VALUES	('000000000000056','AAAA','2017-10-25 10:11:55.000',3)

INSERT	@Receipt VALUES	('000000000000014','BBBB','2017-11-2 16:12:31.000',3)
INSERT	@Receipt VALUES	('000000000000022','CCCC','2017-11-28 11:11:55.000',3)
INSERT	@Receipt VALUES	('000000000000052','DDDD','2017-10-06 07:40:00.000',3)
INSERT	@Receipt VALUES	('000000000000400','EEEE','2017-12-04 07:56:47.000',3)
;With CTE
AS
(
SELECT r.*,serial,prod_dt,line,pstatus,
DENSE_RANK() OVER(PARTITION BY r.id,receipt_dt ORDER BY pd DESC) AS Seq
FROM (SELECT *,LEAD(receipt_dt,1,'20491231') OVER (PARTITION BY id ORDER BY receipt_dt) AS NxtDate
FROM @Receipt) r
JOIN @Prod p
ON p.id = r.id
AND p.prod_dt BETWEEN r.receipt_dt AND NxtDate
WHERE serial ! = 'NULL'
)
SELECT *
FROM CTE
WHERE Seq = 1



/*
output
-----------------------------------
id	itemdesc	receipt_dt	sourceid	NxtDate	serial	prod_dt	line	pstatus	Seq
---------------------------------------------------------------------------------------------------------
000000000000014	BBBB	2017-11-02 16:12:31.000	3	2049-12-31 00:00:00.000	30061	2017-12-01 08:00:56.000	Line2	Created Order	1
000000000000014	BBBB	2017-11-02 16:12:31.000	3	2049-12-31 00:00:00.000	30061	2017-12-06 09:10:46.000	Line2	Order Complete	1
000000000000022	CCCC	2017-11-28 11:11:55.000	3	2049-12-31 00:00:00.000	30022	2017-12-01 08:41:11.000	Line3	Created Order	1
000000000000052	DDDD	2017-10-06 07:40:00.000	3	2049-12-31 00:00:00.000	3384	2017-12-21 13:15:18.000	1STr	Order Complete	1
000000000000052	DDDD	2017-10-06 07:40:00.000	3	2049-12-31 00:00:00.000	3384	2017-12-23 21:47:47.000	1STr	Created Order	1
000000000000056	AAAA	2017-08-10 10:10:20.000	3	2017-10-25 10:11:55.000	30045	2017-09-25 10:11:55.000	Line1	Created Order	1
000000000000056	AAAA	2017-08-10 10:10:20.000	3	2017-10-25 10:11:55.000	30045	2017-10-15 16:12:31.000	Line1	Order Complete	1
000000000000056	AAAA	2017-10-25 10:11:55.000	3	2049-12-31 00:00:00.000	30045	2017-12-01 08:11:02.000	LineT	Created Order	1
000000000000056	AAAA	2017-10-25 10:11:55.000	3	2049-12-31 00:00:00.000	30045	2017-12-05 17:03:58.000	LineT	Order Complete	1
000000000000400	EEEE	2017-12-04 07:56:47.000	3	2049-12-31 00:00:00.000	3382	2017-12-21 13:49:15.000	1STr	Created Order	1
2

You did a nice job with the sample data and code but a short description of what you're trying to do would be helpful, especially since the code is undocumented.

3

LEAD is new with 2012. Do you have 2012 on the production server?

EDIT:
First, you have too much going on in that CTE. Break that into smaller pieces.
Secondly, to simulate LEAD, you'll do a rec_number() and join to the previous record. That's the standard way, regardless of data. It's possible the data itself will allow a more clever way.