soytsauce - 3 months ago 5x

SQL Question

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

`@StartDate`

`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`

`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.

Source (Stackoverflow)

Comments