cancel
Showing results for 
Search instead for 
Did you mean: 

Query help required

former_member193355
Contributor
0 Kudos
67

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

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member193355
Contributor
0 Kudos

Dear All Experts,

Please give us a help to solve this problem. Thank you.

Regard,

Steve

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

You can use below formula to calculate total variance:

Total variance = Actual product cost - Actual component cost


Total variance = (T2.[StockPrice]*((T0.[PlannedQty] * (T2.[Quantity]/T0.[PlannedQty])))) - (T1.[StockPrice]*T1.[Quantity])



Where


Actual product cost =  isnull((T2.[StockPrice]*((T0.[PlannedQty] * (T2.[Quantity]/T0.[PlannedQty])))),0)

Actual component cost = T1.[StockPrice]*T1.[Quantity]

OWOR T0

IGN1 T2

IGE1 T1

Add above field in your query.

Thanks & Regards,

Nagarajan