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