Desktop productivity for business analysts and programmers

Finding a date closest to a date from range

Reply
Occasional Contributor
Posts: 8

Finding a date closest to a date from range

[ Edited ]
 
 
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

 

 

RegionIDMonthYearProductCodeShortageQtyLastChancetoOrderOrderFirstDateMonthsBetweenOrdersNoofOrdersPerYearDeliveryLeadTime
111/1/2018FA-520I3110/1/201715-Sep263
112/1/2018FA-465C611/1/201715-Sep263
112/1/2018FA-520I611/1/201715-Sep263
223/1/2018FA-465C612/1/201715-Sep343
223/1/2018FA-696312/1/201715-Sep343
224/1/2018FA-465C52/1/201815-Sep343
PROC Star
Posts: 1,288

Re: Finding a date closest to a date from range

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)"?

Occasional Contributor
Posts: 8

Re: Finding a date closest to a date from range

Hi Tom

 

The cycles are  'OrderFirstDate' column date + MonthsBetweenOrders

 

Regards

Hessam  

PROC Star
Posts: 1,288

Re: Finding a date closest to a date from range

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

Occasional Contributor
Posts: 8

Re: Finding a date closest to a date from range

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

 

 

Frequent Contributor
Posts: 102

Re: Finding a date closest to a date from range

@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

Occasional Contributor
Posts: 8

Re: Finding a date closest to a date from range

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

 

BucketRegion 22Region 11
115-Sep15-Sep
215-Jan15-Nov
315-Mar15-Jan
415-Jun15-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)

 

RegionIDMonthYearProductCodeShortageQtyLastChancetoOrderOrderFirstDateMonthsBetweenOrdersNoofOrdersPerYearDeliveryLeadTimeReorderBucket
111/1/2018FA-520I3110/1/201715-Sep263 15-Sep-2017
112/1/2018FA-465C611/1/201715-Sep263 15-Sep-2017
112/1/2018FA-520I611/1/201715-Sep263 15-Sep-2017
223/1/2018FA-465C612/1/201715-Sep343 15-Sep-2017
223/1/2018FA-696312/1/201715-Sep343 15-Sep-2017
224/1/2018FA-465C52/1/201815-Sep343 15-Jan-2018
Ask a Question
Discussion stats
  • 6 replies
  • 150 views
  • 0 likes
  • 3 in conversation