So I figured this out like this.

The first query is everything in the PartDtl table, only displaying the dates, then group by date, and culculated Row_number field. This gives me a sequential number to use for date ranges that dynamically follows the calendar. It’s possible that nothing is due on a working day, but highly unlikely in our business case.

The next query in the partdtl table joined to my row number table by due date. I sum everything by day on this table. I also add a calculated field for the vendor supply days. In my example, I just used 3, but I will be adding a field in the part master for vendor managed days of supply so that each part will have it’s own.

The top level, I bring in the 2nd query twice, and join it with a range. This gives me the duplicate rows that I need to sum up everything within the range. So I sum the quantity from the 2nd table and group by the information on the first table. This effectively gives me a 3 day usage sum for any single day.

In addition to the vendor days of supply I will also be adding a bin qty so I have a threshold to know when to display this row and when to hide it.

```
select
[DailySum].[PartDtl1_PartNum] as [PartDtl1_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[DailySum].[Calculated_RowNum] as [Calculated_RowNum],
[DailySum].[PartDtl1_DueDate] as [PartDtl1_DueDate],
(sum(DailySum1.Calculated_DailySum)) as [Calculated_threedaysum],
[DailySum].[Calculated_DailySum] as [Calculated_DailySum]
from (select
[PartDtl1].[Company] as [PartDtl1_Company],
[PartDtl1].[PartNum] as [PartDtl1_PartNum],
[PartDtl1].[DueDate] as [PartDtl1_DueDate],
[DateTable].[Calculated_DateRange] as [Calculated_DateRange],
[DateTable].[Calculated_RowNum] as [Calculated_RowNum],
(sum(PartDtl1.Quantity )) as [Calculated_DailySum],
(DateTable.Calculated_RowNum + 3) as [Calculated_DaysOfSupply]
from Erp.PartDtl as PartDtl1
inner join (select
[PartDtl].[Company] as [PartDtl_Company],
[PartDtl].[DueDate] as [PartDtl_DueDate],
(Row_Number () over (order by PartDtl.DueDate)) as [Calculated_RowNum],
(RowNum + 3) as [Calculated_DateRange]
from Erp.PartDtl as PartDtl
group by [PartDtl].[Company],
[PartDtl].[DueDate]) as DateTable on
PartDtl1.Company = DateTable.PartDtl_Company
and PartDtl1.DueDate = DateTable.PartDtl_DueDate
group by [PartDtl1].[Company],
[PartDtl1].[PartNum],
[PartDtl1].[DueDate],
[DateTable].[Calculated_DateRange],
[DateTable].[Calculated_RowNum]) as DailySum
inner join (select
[PartDtl1].[Company] as [PartDtl1_Company],
[PartDtl1].[PartNum] as [PartDtl1_PartNum],
[PartDtl1].[DueDate] as [PartDtl1_DueDate],
[DateTable].[Calculated_DateRange] as [Calculated_DateRange],
[DateTable].[Calculated_RowNum] as [Calculated_RowNum],
(sum(PartDtl1.Quantity )) as [Calculated_DailySum],
(DateTable.Calculated_RowNum + 3) as [Calculated_DaysOfSupply]
from Erp.PartDtl as PartDtl1
inner join (select
[PartDtl].[Company] as [PartDtl_Company],
[PartDtl].[DueDate] as [PartDtl_DueDate],
(Row_Number () over (order by PartDtl.DueDate)) as [Calculated_RowNum],
(RowNum + 3) as [Calculated_DateRange]
from Erp.PartDtl as PartDtl
group by [PartDtl].[Company],
[PartDtl].[DueDate]) as DateTable on
PartDtl1.Company = DateTable.PartDtl_Company
and PartDtl1.DueDate = DateTable.PartDtl_DueDate
group by [PartDtl1].[Company],
[PartDtl1].[PartNum],
[PartDtl1].[DueDate],
[DateTable].[Calculated_DateRange],
[DateTable].[Calculated_RowNum]) as DailySum1 on
DailySum.PartDtl1_PartNum = DailySum1.PartDtl1_PartNum
and DailySum.Calculated_RowNum <= DailySum1.Calculated_RowNum
and DailySum.Calculated_DateRange >= DailySum1.Calculated_RowNum
inner join Erp.Part as Part on
DailySum.PartDtl1_Company = Part.Company
and DailySum.PartDtl1_PartNum = Part.PartNum
and ( Part.TypeCode = 'P' )
group by [DailySum].[PartDtl1_PartNum],
[Part].[PartDescription],
[DailySum].[Calculated_RowNum],
[DailySum].[PartDtl1_DueDate],
[DailySum].[Calculated_DailySum]
```