0

I want to create a query for a find difference value between Quo QTY and INV QTY and display in the last group row. For example, in this picture, I want to query a display like this.

enter image description here

but now it displays like this.

enter image description here


This is my code.

declare @num VARCHAR(15)
set @num = '5100074611'
select T0.NumAtCard 'PO No',T0.DocNum 'Quotation No', T0.CardName 'Customer Name', T1.ItemCode 'Quo Item', 
       T1.Quantity 'Quo QTY',T2.DocNum 'INV No', T3.ItemCode 'INV Item', T3.Quantity 'INV QTY', 
       T1.Quantity - (select SUM(T3.Quantity) 
                      from INV1 T3 INNER JOIN OINV T2 ON T0.NumAtCard = T2.U_PONUM 
                      where T2.DocEntry = T3.DocEntry AND T1.ItemCode = T3.ItemCode 
                      group by T2.NumAtCard) 'Diff'
from OQUT T0
    INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry
    LEFT JOIN OINV T2 ON T0.NumAtCard = T2.U_PONUM
    LEFT JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry AND T1.ItemCode = T3.ItemCode
where T0.NumAtCard = @num

I use the SQL server 2008 can't use 'order by' in OVER clause. Could you suggest me a way to solve this issue and thank you in advance.

  • 1
    Post table definitions in terms of CREATE TABLE statements, and INSERT statements with sample data that illustrates the problem. Another option is to create a dbfiddle.uk (oldest is 2012 though) – Lennart Nov 8 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.