How can I refer to a LAG() function column in SQL Server?-CodePudding

I have a query in which I use LAG function :

WITH Tr AS 
(
    SELECT 
        DocDtls.Warehouse, Transactions.Code, DocDtls.zDate, 
        Transactions.ID, Transactions.QtyIn, Transactions.QtyOut, 
        Transactions.BalanceAfter
    FROM 
        DocDtls 
    INNER JOIN 
        Transactions ON DocDtls.[PrimDocNum] = Transactions.[DocNum]
)
SELECT
    ID, Code, QtyIn, QtyOut, BalanceAfter, 
    LAG(BalanceAfter, 1, 0) OVER (PARTITION BY Warehouse, Code 
                                  ORDER BY Code, ID) Prev_BlncAfter 
FROM 
    Tr;

It's working fine but when I try to add this column before FROM:

SUM(Prev_BlncAfter   QtyIn) - QtyOut AS NewBlncAfter

I get this error :

Msg 207, Level 16, State 1, Line 3
Invalid column name 'Prev_BlncAfter'

How can I fix this ? Thanks

CodePudding user response：

You can create the LAG column inside the CTE instead of in the outer query. E.g.

declare @DocDtls table (Warehouse int, zDate date, [PrimDocNum] int);
declare @Transactions table (code int, id int, QtyIn int, QtyOut int, balanceafter int, [DocNum] int)
;with Tr As 
(
    SELECT 
        d.Warehouse
        , t.Code
        , d.zDate
        , t.ID
        , t.QtyIn
        , t.QtyOut
        , t.BalanceAfter
        ,LAG(BalanceAfter,1,0) Over (partition by Warehouse,Code order by Code,ID) Prev_BlncAfter
    FROM @DocDtls d
    INNER JOIN @Transactions t ON d.[PrimDocNum] = t.[DocNum]
)
select ID,Code,QtyIn,QtyOut,BalanceAfter
,SUM(Prev_BlncAfter   QtyIn)-QtyOut As NewBlncAfter
 from Tr 
 group by ID,Code,QtyIn,QtyOut,BalanceAfter;

CodePudding user response：

You can nest this query to refer the newly added column from the outer scope, or create another with like you've done before for referencing it afterwards:

with Tr As (
  SELECT
    DocDtls.Warehouse,
    Transactions.Code,
    DocDtls.zDate,
    Transactions.ID,
    Transactions.QtyIn,
    Transactions.QtyOut,
    Transactions.BalanceAfter
  FROM
    DocDtls
    INNER JOIN Transactions ON DocDtls.[PrimDocNum] = Transactions.[DocNum]
),
formatted_tr as (
  select
    ID,
    Code,
    QtyIn,
    QtyOut,
    BalanceAfter,
    LAG(BalanceAfter, 1, 0) Over (
      partition by Warehouse,
      Code
      order by
        Code,
        ID
    ) Prev_BlncAfter
  from
    Tr
  )
select
  SUM(Prev_BlncAfter   QtyIn) - QtyOut As NewBlncAfter
from
  formatted_tr
group by 
  ID, QtyOut
;

CodePudding user response：

Based on comments , I combined the two answers to get what I need :

with Tr As (
  SELECT
    DocDtls.Warehouse,
    Transactions.Code,
    DocDtls.zDate,
    Transactions.ID,
    Transactions.QtyIn,
    Transactions.QtyOut,
    Transactions.BalanceAfter
  FROM
    DocDtls
    INNER JOIN Transactions ON DocDtls.[PrimDocNum] = Transactions.[DocNum]
),
formatted_tr as (
  select
    ID,
    Code,
    QtyIn,
    QtyOut,
    BalanceAfter,
    LAG(BalanceAfter, 1, 0) Over (
      partition by Warehouse,
      Code
      order by
        Code,zDate,ID
    ) Prev_BlncAfter
  from
    Tr
  )
select ID,Code,QtyIn,QtyOut,BalanceAfter
,SUM(Prev_BlncAfter   QtyIn)-QtyOut As NewBlncAfter
 from formatted_tr
 group by ID,Code,QtyIn,QtyOut,BalanceAfter;
;