When i am executing below query in sql 2008 server ,it is giving error
Msg 195, Level 15, State 10, Line 19
'LAG' is not a recognized built-in function name.
Below is query
;WITH wte1 AS
(
SELECT a.EntryDate, a.pWeight, a.prdqty, b.Bweight, b.Bpqty, c.iWeight, c.QTY, 35400+SUM(iWeight - pWeight - Bweight) OVER (ORDER BY a.EntryDate) Closing
FROM ( SELECT SUM(pWeight) pWeight,SUM(prdqty) prdqty, EntryDate FROM #Probale GROUP BY EntryDate) a
JOIN ( SELECT SUM(Bweight) Bweight,SUM(Bpqty) Bpqty, EntryDate FROM #Bigbalprd GROUP BY EntryDate) b ON a.EntryDate = b.EntryDate
JOIN ( SELECT SUM(iWeight) iWeight,SUM(QTY) QTY, EntryDate FROM #ConIssuance GROUP BY EntryDate) c ON a.EntryDate = c.EntryDate
)
,
-- wte2: lag closing to get opening
wte2 AS
(
select EntryDate, pWeight,prdqty, Bweight,Bpqty, iWeight,QTY, Closing Closing, LAG(CLOSING,1,35400) OVER(ORDER BY EntryDate) Opening FROM wte1
),
-- wte3: get floor by adding opening to iWeight of that day
wte3 AS
(
select EntryDate, pWeight,prdqty, Bweight,Bpqty, iWeight,QTY, Opening+iWeight [Floor] , Closing
, Opening FROM wte2
)
-- final result
SELECT * FROM wte3
