Finding a date closest to a date from range

[Hessam]

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

[TomKari]

It looks like we know the date that a product must be ordered by (LastOrderChance), but how do we know the "order cycle for that region / warehouse is quarterly (Jan, Mar, Sep, Dec)"?

[Hessam]

Hi Tom

The cycles are  'OrderFirstDate' column date + MonthsBetweenOrders

Regards

Hessam  

[TomKari]

Taking the first line, if I understand your requirements you want to order ProductCode FA-5201 on or before Oct 1, 2017. But your "OrderFirstDate" is Sep 15, 2018. So there's no way to add "MonthsBetweenOrders" to that to come to less than "LastOrderChance".

[Hessam]

I corrected the dataset actually the first order should refer to the month and day, any year. 

[MarkWik]

@Hessam You could show a sample of your expected OUTPUT explaining a the logic referring to it. Otherwise, it's gonna go back and forth

[Hessam]

Given the dataset, each region's reorder points bucket (based on month) is:

Bucket	Region 22	Region 11
1	15-Sep	15-Sep
2	15-Jan	15-Nov
3	15-Mar	15-Jan
4	15-Jun	15-Mar
5		15-May
6		15-Jul

Expectation is to assign those products and their quantities (whose LastChancetoOrder date falls between any bucket) to the closest bucket before LastChancetoOrder 

 The result should look like below (last column ReorderBucket)

RegionID	MonthYear	ProductCode	ShortageQty	LastChancetoOrder	OrderFirstDate	MonthsBetweenOrders	NoofOrdersPerYear	DeliveryLeadTime	ReorderBucket
11	1/1/2018	FA-520I	31	10/1/2017	15-Sep	2	6	3	15-Sep-2017
11	2/1/2018	FA-465C	6	11/1/2017	15-Sep	2	6	3	15-Sep-2017
11	2/1/2018	FA-520I	6	11/1/2017	15-Sep	2	6	3	15-Sep-2017
22	3/1/2018	FA-465C	6	12/1/2017	15-Sep	3	4	3	15-Sep-2017
22	3/1/2018	FA-696	3	12/1/2017	15-Sep	3	4	3	15-Sep-2017
22	4/1/2018	FA-465C	5	2/1/2018	15-Sep	3	4	3	15-Jan-2018