remove duplicate rows

[Former Member]

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

[former_member212181 · Accepted solution]

Hi,

Please try below query

Select

  tb1.DocNum,

  tb1.ItemName,

  tb1.PostDate,

  tb1.PlannedQty,

  tb1.CmpltQty,

  tb1.[Actual Component Cost],

  tb1.[FG Value]-tb1.[By Product Value] [Actual FG Value],

  tb1.[By Product Value],

  tb1.[Additional Cost],

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

from

(

  SELECT

   T0.DocNum,

   T0.PlannedQty,

   T0.CmpltQty,

   T3.ItemName,

   T0.PostDate,

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

(select sum(Tx2.TransValue) from oinm tx2  where tx2.ApplObj=202 and tx2.AppObjAbs=t0.DocEntry AND tx2.TransValue>0 and tx2.ItemCode = T0.ItemCode )as 'FG Value',

(select sum(Tx2.TransValue) from oinm tx2  where tx2.ApplObj=202 and tx2.AppObjAbs=t0.DocEntry AND tx2.TransValue>0 and tx2.ItemCode <> T0.ItemCode )as 'By Product Value',

(select -sum(Tx2.TransValue) from oinm tx2  where tx2.ApplObj=202 and 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 T1.BaseType=202 and T0.DocEntry = T1.BaseEntry

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

   Inner Join OITM T3 on T0.ItemCode = T3.ItemCode

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

GROUP BY

T0.ItemCode,

T0.DocNum,

T0.PlannedQty,

T0.CmpltQty,

T3.ItemName,

T0.PostDate,

--(T1.StockPrice*T0.CmpltQty),

t0.docentry,AppObjAbs

)as tb1

Order by 1

Thanks

Unnikrishnan

[KennedyT21]

Try This

Select

  tb1.DocNum,

  tb1.Dscription,

  tb1.PostDate,

  tb1.PlannedQty,

  tb1.CmpltQty,

  tb1.[Actual Component 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,

(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,

t0.docentry,AppObjAbs) as tb1

order by tb1.DocNum