Hi,
I like to know how to use SQL query to get the Standard cost of an item for a certain date.
Any suggestions much appreciated.
Shyam
It makes sense to look for a cost on a certain date when dealing with average costing method, but standard cost is not bound to a period. It takes effect whenever standard cost amount is changed in the item card. So, generally speaking every item ledger entry can have its own standard cost.
There is no historic records of cost updates if you don’t use logging, so it has to be calculated from value entries.
This is how it can be done
select [Item Ledger Entry No_], [Valuation Date], SUM([Cost per Unit]) from dbo.[CRONUS International$Value Entry]
where [Item No_] = ‘SomeItemNo’ and Positive = 1 and [Entry Type] in (0, 4)
group by [Item Ledger Entry No_], [Valuation Date]
Here, I select sum of cost per unit of value entries assigned to the same item ledger entry. Only positive records are taken, because outbound cost can differ from current standard cost when an outbound entry is applied to several cost sources with different costs. Besides I filter entry type leaving only direct cost and variance, taking out all indirect cost amounts, rounding entries and revaluation.
As a result you’ll have a list of item ledger entries with corresponding standard costs, then you can filter them further by date if you need cost on a date.
you could start with table “Average Cost Calc. Overview” (5847). check out page 5847 “Average Cost Calc. Overview”.