Production Order wise amount variance query report

[hemabhushan_g]

Dear Experts,

I need a production order wise amount variance report with below mentioned format. please help us to execute the query without duplicate lines.

1. Duplicate not allowed

2. Multiple GI & GR done for single PO

3. My query attached for your reference

SELECT A.DOCNUM

,case when A.Status = 'L' then 'Closed' when A.Status = 'C' then 'Canceled'

when A.Status = 'P' then 'Planned' when A.Status = 'R' then 'Released' end [Prod Status]

,case when A.Type = 'D' then 'Disassembly' when A.Type = 'P' then 'Special' when A.Type = 'S' then 'Standard' end [Prod Type]

,CONVERT(DATE,A.PostDate,103) [Prod Date],CONVERT(DATE,A.DueDate,103) [Prod DueDate]

,A.ItemCode,A.ProdName,A.PlannedQty,A.CmpltQty

,(A.PlannedQty-A.CmpltQty) [Open Qty]

,(C.[GR value]-D.[GI Value]) [Diff value]

FROM OWOR A INNER JOIN WOR1 B ON A.DOCENTRY = B.DOCENTRY

LEFT JOIN (

select SUM(d.DocTotal) [GR Value],C.BASEREF

from IGN1 c with(nolock) inner join OIGN d with(nolock) on d.DocEntry = c.DocEntry

where c.U_BaseType = 'WIP' and c.BaseRef = '94941'

GROUP BY C.BASEREF) C ON C.BASEREF = A.DOCNUM

LEFT JOIN (

select SUM(d.DocTotal) [GI Value],C.BASEREF

from IGE1 c with(nolock) inner join OIGE d with(nolock) on d.DocEntry = c.DocEntry

INNER JOIN OWOR E ON C.BaseRef = E.DocNum

where c.U_BaseType = 'WIP' and c.BaseRef = '94941'

GROUP BY C.BASEREF,E.DocNum) D ON D.BASEREF = A.DOCNUM

WHERE A.DOCNUM = '94941'

GROUP BY A.DOCNUM,C.[GR VALUE],D.[GI VALUE],A.STATUS,A.TYPE,A.POSTDATE,A.DUEDATE,A.ITEMCODE,A.PRODNAME,A.PLANNEDQTY,A.CMPLTQTY