Query help required

Dear Experts,

I have a sample query belows:

WITH receipt AS (

SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,

ROW_NUMBER() OVER (partition by owor.docentry

ORDER BY owor.docentry) as row,owor.docentry

FROM ign1 left join OWOR on owor.DocNum=ign1.BaseRef and ign1.ItemCode=owor.ItemCode

inner join OIGN on oign.DocEntry=ign1.DocEntry

GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum, owor.DocEntry, oign.DocDate

),

issued AS (

SELECT owor.DocEntry,

ROW_NUMBER() OVER (partition by owor.docentry

ORDER BY owor.docentry ) as iss_row,

OIGe.DocNum, ige1.BaseRef, sum(Isnull(ige1.linetotal,0)) as total_issue

FROM ige1 inner join OWOR on owor.DocEntry= ige1.BaseEntry and IGE1.BaseType = '202'

inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry

group by ige1.BaseRef, oige.DocNum,oige.docentry,owor.DocEntry

)

select

TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Num],Isnull(TBL2.[Issued Amt],0) [Issued Amt],TBL2.[Receipt Num],TBL2.[Receipt Amt]

from (

Select (CASE rnum when 1 then ItemCode end) [FG-Code],

(CASE rnum when 1 then Isnull(DocNum,0) end) [DocNum],

(CASE ISrnum when 1 then Isnull(Issued_No,0) end) [Issued Num],

(CASE ISrnum when 1 then Isnull(issuedamount,0) end) [Issued Amt],

(CASE RSrnum when 1 then Isnull(Receipt_No,0) end) [Receipt Num],

(CASE RSrnum when 1 then Isnull(receiptamount,0) end) [Receipt Amt],

(forshort) [forshort]

from (

SELECT owor.docentry,ROW_NUMBER() OVER (partition by owor.docentry order by owor.docentry) [rnum] ,

owor.docentry [forshort],

ROW_NUMBER() OVER (partition by issued.DocNum order by issued.DocNum) [ISrnum],

ROW_NUMBER() OVER (partition by receipt.DocNum order by receipt.DocNum) [RSrnum],

owor.ItemCode, owor.DocNum, issued.DocNum as Issued_No,

max(Isnull(issued.total_issue,0)) AS issuedamount,

receipt.DocNum as Receipt_No,

max(receipt.total_receipt) AS receiptamount

FROM wor1 inner join owor on owor.DocEntry = wor1.docentry

inner join OITM on oitm.itemcode = owor.ItemCode

left outer join receipt

on receipt.BaseRef = owor.docnum

left outer join issued

on isnull(issued.DocEntry,0) = isnull(owor.DocEntry,0)

GROUP BY owor.docentry,owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum,receipt.row ,issued.iss_row ) TBL1 ) TBL2

where tbl2.docnum ='[%1]'

group by

TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Amt],TBL2.[Issued Num],TBL2.[Receipt Num],TBL2.[Receipt Amt]

having isnull(TBL2.[Receipt Amt],0) - isnull(TBL2.[Issued Amt],0) <> 0

order by

Max(TBL2.forshort) , TBL2.DocNum desc

I want to add total variance of production order for each production order number column in the query. Please help how to calculate it in the above query.

I appreciate your help so much

THank you

Regards,

Steve