I have the LastOrderChance date meaning the latest time a product should be ordered to avoid shortage in that warehouse. My challenge is how do i group the products which needs to be ordered into the closest order cycle (the time as per the regular ordering points) so the shortages are added into those order cycles. For example if a product must be ordered by Oct 2018 and the order cycle for that region / warehouse is quarterly (Jan, Mar, Sep, Dec), the qty and product should be added to the September order. The closest or better to say the right order cycle should be shown in a new column for each row. Min_Inventory_Count Table RegionID; region indicator MonthYear; the month and year for the record ProductCode; the product which the record is about ShortageQty, the number of units in shortage from that product in the month and year (MonthYear column) LastChancetoOrder; this is a calculated date backward of the MonthYear minus the lead time in months. This columns tells which date at latest the product must be ordered given the delivery lead time so qty in shortage is arrived in the current MonthYear ReOrderStartDate; this columns is brought from the RegionDate table and is the date of the first order in the year for that warehouse/region. MonthsBetweenOrders; the number of months interval between the first order and second (following) orders NoofOrdersPerYear; also brought from the other table, indicates the number of annual orders for each warehouse/region DeliveryLeadTime; brought from the other table, shows the delivery lead time for the product to that warehouse RegionID MonthYear ProductCode ShortageQty LastChancetoOrder OrderFirstDate MonthsBetweenOrders NoofOrdersPerYear DeliveryLeadTime 11 1/1/2018 FA-520I 31 10/1/2017 15-Sep 2 6 3 11 2/1/2018 FA-465C 6 11/1/2017 15-Sep 2 6 3 11 2/1/2018 FA-520I 6 11/1/2017 15-Sep 2 6 3 22 3/1/2018 FA-465C 6 12/1/2017 15-Sep 3 4 3 22 3/1/2018 FA-696 3 12/1/2017 15-Sep 3 4 3 22 4/1/2018 FA-465C 5 2/1/2018 15-Sep 3 4 3
... View more