Skip to Content
0

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

Dec 06, 2016 at 06:38 AM

82

avatar image

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
10 |10000 characters needed characters left characters exceeded

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




0

Hi experts

Any updates ??

Regards

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Johan Hakkesteegt Dec 08, 2016 at 12:52 PM
0

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
Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thanks Johan

This query works, but i had a issue ,

when i make the total Value (Unit Cost * Unit Balance ) as Total Value 'and sum the Total value

the amount is not matching with the GL balance of the Whs

for one whs its showing 700000 difference and another around 300000 difference

how can i make it correct

Regards

0

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

0
Johan Hakkesteegt Dec 07, 2016 at 08:02 AM
0

Hi,

OITW.AvgPrice is the correct price.

Please join OITW like this:

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

Regards,

Johan

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi Mr John

I made like this, but now am getting all Null value

in Unit Price field


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 OITM.ItemCode = OITW.ItemCode AND OITM. DfltWH = 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



Regards

0

Please change this:

avg(OITW.AvgPrice)

to this:

avg(ISNULL(OITW.AvgPrice, 0))
0

Hi johan

i had changed it , but now all the Unit Price is '0'

But actually there is average value Showing in Item master data, Inventory tab

this is how i modified








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 OITM. DfltWH = 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


Regards

0

Hi Johan

Any Solution Sir ?

Regards

0