soytsauce soytsauce - 5 months ago 17
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]

Answer

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.