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.
but now it displays like this.
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.