0

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Csharp Newbie
  • 303
  • 1
  • 10
  • 8
    The message is correct - it's nothing to do with `lag()`, it's the `order by` in your *common table expression* that's not allowed; the only ordering that Lag relies on is its own order-by clause. – Stu May 05 '22 at 19:19
  • @Stu Thanks for your reply , How can I fix this ? the order is essential to depend on without it the `LAG` will give wrong results. – Csharp Newbie May 05 '22 at 19:24
  • 2
    Also - in addition to the fact that `LAG()` couldn't possibly rely on any ordering you implement inside the CTE - the order by there CTE makes no sense anyway. `Warehouse` and `zDate` aren't even returned in the outer query, what is the purpose of ordering by them anywhere? If they are important to the `LAG` determination then they go inside its `OVER()` clause, not in some unrelated (and invalid) place. – Aaron Bertrand May 05 '22 at 19:25
  • @CsharpNewbie can you demonstrate this dependency with some sample data and expected results, perhaps in a SQL Fiddle? – Stu May 05 '22 at 19:26
  • 3
    (To prove the point about the error having nothing to do with `LAG`, remove `LAG` altogether and the error persists.) – Aaron Bertrand May 05 '22 at 19:27
  • The same code could exist in different warehouses , if I didn't order by warehouse the result will be mixed and I couldn't do the desired calculation , – Csharp Newbie May 05 '22 at 19:28
  • @AaronBertrand You are absolutely right I tried and the error persists. – Csharp Newbie May 05 '22 at 19:29
  • @Stu I will try Sql Fiddle to demonstrate . – Csharp Newbie May 05 '22 at 19:29
  • 2
    `LAG` doesn't change or depend on how the data set is ordered; the CTE is essentially a derived table that's consumed by the outer/parent query and like all tables there is no implied orrdering, a table is an unordered set and operations on an unordered set with window functions operate the same regardless. – Stu May 05 '22 at 19:31
  • @Stu I added some data in Edit#1 please have a look. – Csharp Newbie May 05 '22 at 19:58
  • 2
    You `lag` is only ordering by `code` which is not unique, it looks like you should be *partitioning* by code and ordering by `id` – Stu May 05 '22 at 20:02

1 Answers1

4

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;
Stu
  • 30,392
  • 6
  • 14
  • 33