Skip to Content
0

Need Urgent Help On Query

Nov 22, 2017 at 11:33 AM

13

avatar image
Former Member

SELECT T0.[ItemCode], T0.[ItemName] , T2.ItmsGrpNam, t3.[AvgPrice],

[WH 100 In Stock] = (Select T3.[OnHand] from OITW T3 where T3.[WhsCode] = '100' and T3.[ItemCode] = T0.[ItemCode]),

[Last Calculated Price] = (SELECT t3.[AvgPrice] from OITW),

[WH 100 Value] = (select T3.[OnHand] * t3.[AvgPrice] from OITW where T3.[WhsCode] = '100' and T3.[ItemCode] = T0.[ItemCode]),

[Last Received] = (SELECT left(Convert(varChar, MAX(T1.[DocDate]) ,111),111) from OINM T1 where T1.[ItemCode] = T0.[ItemCode] and T1.[InQty] > 0 ),

[Last Issued] = (SELECT left(Convert(varChar, MAX(T1.[DocDate]) ,111),111) from OINM T1 where T1.[ItemCode] = T0.[ItemCode] and T1.[OutQty] > 0 )

FROM OITM T0 inner join OITB T2 on T2.ItmsGrpCod = T0.ItmsGrpCod

WHERE [OnHand] in (Select T3.[OnHand] from OITW T3 where T3.[WhsCode] = '100' and T3.[ItemCode] = T0.[ItemCode] and T3.onhand >0)

and T2.ItmsGrpNam >= CASE '[%2]' when '' then 'aaaaaa' Else '[%2]' End and T2.ItmsGrpNam <= CASE '[%3]' when '' Then 'ZZZZZZ' Else '[%3]' end

order by [Last Issued] , t2.ItmsGrpNam, t0.itemcode

I am finding it hard, to bring through average price from OITW - can any experts helps?

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers