question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked GuoxiongYuan-7218 edited

Msg 195, Level 15, State 10, Line 19 'LAG' is not a recognized built-in function name.

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


@MelissaMa-msft

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @akhterhussain-3167,

Please have another try with below:

 ;with cte as (
 SELECT a.EntryDate, a.pWeight, a.prdqty, b.Bweight, b.Bpqty, c.iWeight, c.QTY,  (iWeight - pWeight - Bweight) Closing1
 ,ROW_NUMBER() OVER (ORDER BY a.EntryDate) RN 
   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)
  ,wte1 as (
   select * from cte a
     CROSS APPLY ( SELECT 35400+sum( Closing1) Closing FROM cte b WHERE b.EntryDate <= a.EntryDate) x)
 ,wte2 AS
 (
 select a.EntryDate, a.pWeight,a.prdqty, a.Bweight,a.Bpqty, a.iWeight,a.QTY, a.Closing
 ,case when b.Closing is null then 35400 else b.Closing end opening 
 from wte1 a
 left join wte1 b 
 on a.RN = b.RN + 1)
 ,
 wte3 AS
 (
 select EntryDate, pWeight,prdqty, Bweight,Bpqty, iWeight,QTY, Opening+iWeight [Floor] , Closing
 , Opening FROM wte2
 )
                    
 SELECT * FROM wte3

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered akhterhussain-3167 commented

When i am executing below query in sql 2008 server ,it is giving error 'LAG' is not a recognized built-in function name.

The LAG (Transact-SQL) function was first introduced in SQL Server 2012, not available in SQL Server 2008.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

so is there any alternative.

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

Since SQL 2008 does not support the function LAG, you can use the alternate way to achieve your task. See the following examples:

 DECLARE @tbl TABLE (
  EntryDate date,
  Closing int
 );
 INSERT INTO @tbl VALUES
 ('2021-05-01', 10), ('2021-05-02', 20), ('2021-05-03', 30),
 ('2021-05-04', 40), ('2021-05-05', 50);
    
 -- Use a buildin function Lag for SQL 2012 or higher
 SELECT EntryDate, Closing, LAG(Closing, 1, 25) OVER(ORDER BY EntryDate) AS Opening
 FROM @tbl;
    
 -- Use an alternate way instead of LAG
 ;WITH CTE AS (
  SELECT EntryDate, Closing, ROW_NUMBER() OVER(ORDER BY EntryDate) AS RowNumber
  FROM @tbl
 ) 
    
 SELECT c1.EntryDate, c1.Closing, ISNULL(c2.Closing, 25) AS Opening
 FROM CTE AS c1 
 LEFT JOIN CTE AS C2 ON c1.RowNumber = c2.RowNumber + 1;

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I used Row_NUmber ,but bellow error is coming.

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'order'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'b'.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'c'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ','.


  select EntryDate, pWeight,prdqty, Bweight,Bpqty, iWeight,QTY, Closing, Row_Number()
   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




0 Votes 0 ·

The error was not from the code you posted.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered OlafHelper-2800 commented

Hi @akhterhussain-3167,

Please refer below which is alternative of LAG.

 ;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
 ,ROW_NUMBER() OVER (ORDER BY a.EntryDate) RN 
 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 a.EntryDate, a.pWeight,a.prdqty, a.Bweight,a.Bpqty, a.iWeight,a.QTY, a.Closing
 ,IIF(b.Closing is null,35400,b.Closing) opening 
 from wte1 a
 left join wte1 b 
 on a.RN = b.RN + 1)
 ,
 -- 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

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

On this line

  ,(IIF(b.Closing is null,35400,b.Closing) opening  

Raising error Below.
IIF is not recognize built in Function Name


0 Votes 0 ·

Hi @akhterhussain-3167,

Please replace it with below:

 ,case when b.Closing is null then 35400 else b.Closing end opening 

Best regards,
Melissa

0 Votes 0 ·

Now there errors are coming .

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'order'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'b'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'c'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ','.

Order syntax is coming on below line.

  , 35400+SUM(iWeight - pWeight - Bweight) OVER (ORDER BY a.EntryDate) Closing


0 Votes 0 ·

IIF is not recognize built in Function Name

Also the IIF function is not available in SQL Server 2008. Always referre to MS Books Online, it's all well documented.



0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @akhterhussain-3167,

Please refer below updated one:

 ;with cte as (
 SELECT a.EntryDate, a.pWeight, a.prdqty, b.Bweight, b.Bpqty, c.iWeight, c.QTY,  (iWeight - pWeight - Bweight) Closing1
 ,ROW_NUMBER() OVER (ORDER BY a.EntryDate) RN 
   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)
  ,wte1 as (
   select * from cte a
     CROSS APPLY (   SELECT 35400 + ISNULL(SUM(v), 0)
                     FROM (  SELECT top(6) Closing1
                             FROM cte b
                             WHERE b.EntryDate <= a.EntryDate
                             ORDER BY b.EntryDate ) x(v)
                 ) x(Closing)),
 wte2 AS
 (
 select a.EntryDate, a.pWeight,a.prdqty, a.Bweight,a.Bpqty, a.iWeight,a.QTY, a.Closing
 ,case when b.Closing is null then 35400 else b.Closing end opening 
 from wte1 a
 left join wte1 b 
 on a.RN = b.RN + 1)
 ,
 wte3 AS
 (
 select EntryDate, pWeight,prdqty, Bweight,Bpqty, iWeight,QTY, Opening+iWeight [Floor] , Closing
 , Opening FROM wte2
 )
                    
 SELECT * FROM wte3

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PLease Below is ouput of my Data,in which i highlighted in red 29867 is correctcclosing ,but in yellow closing is coming wrong by using last query,
I manually calculated closing in red but in yellow system is auto calculating.


@MelissaMa-msft

99786-image.png


0 Votes 0 ·
image.png (63.8 KiB)