Query - Stock Value (On a Selected Date)

[Former Member]

Hi all

I came up with the below query to display the stock value at a given date in the past.The total value is correct as per the Inventory Audit report. However, it displays the total value for the two warehouses that we have, yet I want it to display the value separately per warehouse. My chart of accounts has only one (general) account defined.

SELECT T0.[Debit]-T0.[Credit] FROM [dbo].[JDT1] T0 WHERE T0.[Account] ='XXXX' AND T0.[RefDate] <='%0'

Anyone with an idea about how I can I can display the values separately? Thank you.

Regards,

Henry

[Former Member · Accepted solution]

Hi Henry,

What is your B1 version and PL?

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

I am on Version 8.81 PL 6.

Regards,

Henry

[Former Member · Accepted solution]

Try this first:

SELECT T0.Warehouse, T0.StockAct, T0.TransValue,

T0.CostAct,T0.Cogsval from oinm T0

WHERE T0.DocDate < [%0\]

[Former Member · Accepted solution]

Hi Gordon,

Many thanks, it worked great.. I'm able to include warehouse selection too.

out of curiosity, what does the T0.Cogsval contain?

Regards,

Henry

[Former Member · Accepted solution]

That is I put in for you to find it out. It is useful for analyzing your cost of goods sold.