GI SUM of Days without Stock

Hi @Jeff96 ,

You could the DateInfo table as a starting table to get the 365 dates.  The tricky bit after that is to get the total item quantity by warehouse (possibly with multiple locations) for any particular date.

The INItemSiteHistDay table only contains records for the dates where there is a change. 

Maybe someone else knows of a different DAC that already has the daily quantities.  In that case you should be able to do this in a GI.  Otherwise, I think would be easier and more efficient in a report. There you can sort the InItemSiteHistDay records by date and use variables to do some calculations.

A basic design might be something like this:

• Cross join DateInfo with INItemSiteHistDay
• Group by [InItemSiteHistDay.LocationID] and sort by [INItemSiteHistDay.sdate].
• Create variables for the DailyLocQty and the DailyWHQty. 
• In the detail section - if sdate is prior to DateInfo.Date, set the DailyLocQty = EndQty.  Since the records are sorted by date this will get you the last EndQty for that location. 
• In the footer for the locationid - add the DailyLocQty to the DailyWHQty
• In the footer for [DateInfo.Date] = if the DailyWHQty = 0 add 1 to the count

Hope this give you some ideas!

Laura