how to query average item cost of an item on a given date.

Hi ,

We have a requirement to develop an inventory aging report for our client.Client follows Average costing method.

One of the key logic to employ in this development is to extract on hand qty and on hand value of items as of given date.(this date is mostly a date in past)

To calculate the on hand values of items , logic has to consider appropriate item cost as of given date .

Kindly help with sql function, tables , Logic explanation -

1. item cost to be considered for calculating on hand value of items on any given date in average costing organization as cost keeps fluctuating.