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

Question

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

Does this answer your question? [Reference an alias elsewhere in the SELECT list](https://stackoverflow.com/questions/11975749/reference-an-alias-elsewhere-in-the-select-list) — Stu, May 21 '22 at 12:46
Thanks for your reply , We already fixed the issue of alias but calculating problem appeared . — Csharp Newbie, May 21 '22 at 12:57

score 2 · Answer 1 · answered May 21 '22 at 11:49

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;

Thanks for replying , I need to use it in C# this syntax will work ? Also the query returned nothing even if I changed the datatypes to my actual datatypes. — Csharp Newbie, May 21 '22 at 12:00
And once again for the people in the back who just can't admit how much they're overcompensating for this insignificant style difference: https://sqlblog.org/cte — Aaron Bertrand, May 21 '22 at 13:29

Josep Pascual · Answer 2 · 2022-05-21T12:06:25.717

1

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
;

edited May 21 '22 at 12:06

answered May 21 '22 at 11:41

Josep Pascual

168
1
7

Thanks for replying , I tried the query but I got this error : "Incorrect syntax near 'DocNum'." – Csharp Newbie May 21 '22 at 11:45
It was a typo "Transactions[DocNum]" I corrected it to "Transactions.[DocNum]" But I got new error "Column 'formatted_tr.QtyOut' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – Csharp Newbie May 21 '22 at 11:48
2

Couple of points `Transactions[DocNum]` should be `Transactions.[DocNum]` - and then you are going to get `Column 'formatted_tr.QtyOut' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` – CHill60 May 21 '22 at 11:48
I was missing a point you're right let me edit it, to fix the ypo, and what makes more sense to obtain that sum I think is to aggregate by ID. – Josep Pascual May 21 '22 at 11:48
Still the same error group by ID didn't fix it. – Csharp Newbie May 21 '22 at 12:03
Well, I tried to add it also to the group by, now should work, but I'm not sure if the resulting output is what you're trying to achieve. – Josep Pascual May 21 '22 at 12:07
You are only outputting the `NewBlncAfter` - the OP wanted other data too. – CHill60 May 21 '22 at 12:15
Thanks for your help , As @CHill60 mentioned that the output should contain `ID`,`Code`,`QtyIn`,`QtyOut`,`NewBlncAfter` , I tried the grouping the error was fixed but the calculation isn't correct I think because of grouping . – Csharp Newbie May 21 '22 at 12:33

score 1 · Answer 3 · edited May 21 '22 at 14:14

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;
;

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

3 Answers3