Why this LAG() Window function returns "The Order by clause is invalid"

Question

Edit #1: Added desired result

Warehouse   ID  Code    QtyIn   QtyOut  BalanceAfter Previous_BlncAfter
MainWh  1   100001  10000   0   10000   0
MainWh  3   100001  0   5000    5000    10000
MainWh  6   100001  3500    0   8500    5000
MainWh  9   100001  0   1500    7000    8500
MainWh  2   100003  15000   0   15000   0
MainWh  4   100003  0   7000    8000    15000
MainWh  7   100003  2200    0   10200   8000
MainWh  10  100003  0   1200    9000    10200
MainWh  5   100005  2500    0   2500    0
MainWh  8   100005  1000    0   3500    2500
MainWh  11  100005  0   1000    2500    3500

I'm trying to execute this query to get the previous BalanceAfter from a select query preparing to preform a calcualtion with the current QtyIn And QtyOut:

with Tr As (
select 
  DocDtls.Warehouse, Transactions.Code, DocDtls.zDate, 
  Transactions.ID, Transactions.QtyIn, Transactions.QtyOut, Transactions.BalanceAfter
FROM DocDtls 
JOIN Transactions ON DocDtls.[DocNum] = Transactions.[DocNum]
ORDER BY DocDtls.Warehouse, Transactions.Code, DocDtls.zDate, Transactions.ID
)
select ID,Code,QtyIn,QtyOut,BalanceAfter,
  Lag(BalanceAfter,1,0) Over (order by Code)Prev_BlncAfter 
from Tr;

But I get this error

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

The first Order by is essential and the LAG depends on it so I need to keep it as it is . I searched for a solution but I couldn't get it to work , Thanks

Answer 1

Try the following modified query and see if this works for you?

with Tr as (
    select 
        d.Warehouse, t.Code, d.zDate, 
        t.ID, t.QtyIn, t.QtyOut, t.BalanceAfter
    from DocDtls d
    join Transactions t on d.DocNum = t.DocNum
)
select ID, Code, QtyIn, QtyOut, BalanceAfter,
  Lag(BalanceAfter,1,0) over (partition by Warehouse, Code order by Id) Prev_BlncAfter 
from Tr;