Skip to Content
0
Former Member
Jun 25, 2015 at 07:18 PM

remove duplicate rows

22 Views

How can I remove duplicate rows from this query? Distinct does not work

Select

tb1.DocNum,

tb1.Dscription,

tb1.PostDate,

tb1.PlannedQty,

tb1.CmpltQty,

tb1.[Actual Component Cost],

tb1.[Actual Product Cost],

tb1.[Additional Cost],

((tb1.[Actual Component Cost]+tb1.[Additional Cost])- tb1.[Actual Product Cost]) as Variance


from (

SELECT

T0.DocNum,

T0.PlannedQty,

T0.CmpltQty,

T1.Dscription,

T0.PostDate,

(T1.StockPrice*T0.CmpltQty) as 'Actual Product Cost',

(select -sum(Tx2.TransValue) from oinm tx2 where tx2.AppObjAbs=t0.DocEntry AND OutQTY>0 )as 'Actual Component Cost',

( select sum(a1.comptotal) from WOR1 a1 where a1.DocEntry=t0.DocEntry ) as 'Additional Cost'


FROM [dbo].[OWOR] T0

LEFT JOIN IGN1 T1 ON T0.DocNum=T1.BaseRef

INNER JOIN OINM T2 ON T0.DocEntry=T2.AppObjAbs AND OutQTY>0


WHERE T0.PostDate >='[%0]' AND T0.PostDate <='[%1]'


GROUP BY

T0.DocNum,

T0.PlannedQty,

T0.CmpltQty,

T1.Dscription,

T0.PostDate,

(T1.StockPrice*T0.CmpltQty),t0.docentry,AppObjAbs) as tb1


order by tb1.DocNum