Skip to Content
avatar image
Former Member

HOW TO ADD AVERAGE PRICE IN THE STOCK BALANCE AVAILABLE QUERY ON A PARTICULAR DAY

Hi experts

i had created a query in which if i select a particular date and a particular warehouse

it gives me the stock in hand at the warehouse at that time and date, and it returning the correct values,

But when i add the OITW.AvegPrice, the Available balance is also showing different

Am having my Inventory in Moving Average, what i need now is need to add average price of the available stock in the Warehouse as on the date, even if it is for old days or present days

i had add the average price but its not giving me the exact value

pls help me to edit the query

Regards

select DISTINCT OINM.ItemCode,OITM.ItemName,OINM.Warehouse,OITB.ItmsGrpNam,avg(OITW.AvgPrice) [Unit Price],Sum(InQty-OutQty) [Unit Balance] from OINM
INNER JOIN OITM ON OINM.ItemCode = OITM.ItemCode
INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod
left outer Join OITW ON OINM.ItemCode = OITW.ItemCode
where
TaxDate <=  '2016-12-06 00:00:00.000'  and Warehouse = 'MN-WH'
group by OINM.ItemCode,OITB.ItmsGrpNam,OITM.ItemName,OINM.Warehouse
Order by ItemCode
Add comment
10|10000 characters needed characters exceeded

  • Former Member

    Hi experts

    i try with this query with small modification

    instead of OITW.AvgPrice i usesd OINM.CalCpRICE

    Now the qty is Correct, but the value is different, 
    select  OINM.ItemCode,OITM.ItemName,OINM.Warehouse,OITB.ItmsGrpNam,AVG(OINM.CalcPrice)[Unit Price],Sum(InQty-OutQty) [Unit Balance] from OINM
    INNER JOIN OITM ON OINM.ItemCode = OITM.ItemCode
    INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod
    
    
    where TaxDate <=  '2016-12-31 00:00:00.000'  and Warehouse = 'MN-WH'  
    group by OINM.ItemCode,OITB.ItmsGrpNam,OITM.ItemName,OINM.Warehouse
    Order by ItemCode
    
    
    

  • Former Member

    Hi experts

    Any updates ??

    Regards

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 08, 2016 at 12:52 PM

    Hi,

    Please give this a try:

    select DISTINCT OINM.ItemCode,OITM.ItemName,OINM.Warehouse,OITB.ItmsGrpNam,avg(ISNULL(OITW.AvgPrice,0.0))[Unit Price],Sum(InQty-OutQty) [Unit Balance] from OINM
    INNER JOIN OITM ON OINM.ItemCode = OITM.ItemCode
    INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod
    left  Outer Join OITW ON OITM.ItemCode = OITW.ItemCode AND OINM.Warehouse = OITW.WhsCode
    where
    TaxDate <=  '2016-12-07 00:00:00.000'  and Warehouse = 'MN-WH'
    group by OINM.ItemCode,OITB.ItmsGrpNam,OITM.ItemName,OINM.Warehouse
    Order by ItemCode
    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      Differences like this can be caused in different ways. You will have to investigate:

      • Do you get the differences if you use DocDate instead of TaxDate?
      • Do you still get the differences when running the query with a different warehouse?
      • Do you still get the differences when running the query without the warehouse parameter?
      • Do you still get the differences when running the query with a different TaxDate parameter (a day more or less)?
      • Were manual entries written to the GL account(s)
      • Are you working with multiple currencies, and is the difference caused by a changed or incorrect exchange rate ?

      Regards,

      Johan

  • Dec 07, 2016 at 08:02 AM

    Hi,

    OITW.AvgPrice is the correct price.

    Please join OITW like this:

    OITM.ItemCode = OITW.ItemCode AND OITM. DfltWH = OITW.WhsCode

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded