soytsauce - 3 months ago 5x
SQL Question

# Calculate Average Qty On Hand of Inventory

I'm trying to find the average qty on hand of my inventory over a date range from parameter

`@StartDate`
by averaging the ending qty from each day. I have three tables: a part table, a part transaction table, and a warehouse table, mocked up below.

``````PartNum      |   PartNum     TranDate     TranQty      |   PartNum   OnHandQty
----------   |   ------------------------------------  |  --------------------
P1           |   P1          6/28/2016    5            |   P1        30
P2           |   P1          6/26/2016    3            |   P2        2
|   P1          6/26/2016    -1           |
|   P1          6/15/2016    2            |
|   P2          6/15/2016    1            |
``````

If today is 6/30/2016 and
`@StartDate`
= 6/1/2016, I expect a result like:

``````PartNum    AverageOnHand
------------------------
P1         22.9
P2         1.5
``````

However, I don't know what function would best allow me to get to an appropriate weighted sum which I could divide by the difference in dates. Is there a SumProduct function or similar that I can use here? My code, so far, is below:

``````select
[Part].[PartNum] as [Part_PartNum],
(max(PartWhse.OnHandQty)*datediff(day,max(PartTran.TranDate),Constants.Today)) as [Calculated_WeightedSum],
(WeightedSum/DATEDIFF(day, @StartDate, Constants.Today)) as [Calculated_AverageOnHand]
from Erp.Part as Part
right outer join Erp.PartTran as PartTran on
Part.PartNum = PartTran.PartNum

inner join Erp.PartWhse as PartWhse on
Part.PartNum = PartWhse.PartNum

group by [Part].[PartNum]
``````

I was able to solve this with a sum. First, I multiplied the final quantity on hand by the number of days in the range. Next, I multiplied each change in inventory by the time from `@StartDate` until the `TransDate`.

``````select
[Part].[PartNum] as [Part_PartNum],
(max(PartWhse.OnHandQty)*datediff(day,@StartDate,Constants.Today)-
sum(PartTran.TranQty*datediff(day,@StartDate,PartTran.TranDate))) as [Calculated_WeightedSum],
(WeightedSum/DATEDIFF(day, @StartDate, Constants.Today)) as [Calculated_AverageOnHand]
from Erp.Part as Part
right outer join Erp.PartTran as PartTran on
Part.PartNum = PartTran.PartNum

inner join Erp.PartWhse as PartWhse on
Part.PartNum = PartWhse.PartNum

group by [Part].[PartNum]
``````

Thanks for your help everyone! You really helped me think it through.

Source (Stackoverflow)