calulaction of delays

[Bobje]

Hello,
a question on coding. We deliver orders to stores and want to measure the delay between the order and the delivery. we have three timestamps: order, scan at warehouse and scan at store.
we need to know the delay between all steps.
All stores have one or more closingdays, the warehouse is closed on weekends, there are public holidays,...

so we need to calculate the 'permitted delay'. If order is placed on saturday we have a permitted delay of 2days, if the store that made the order is closed on monday one day should be added.

in case this monday is a public holiday the foreseen delay shouldn't be counted twice of course.
Any ideas on how I could get this in pl/sql procedure?

 

[Hans63]

I would build a table based on the calendar, with all kinds of attributes (weekday, workday, public holiday, name of day, nr of day in the year, etc). Just like the way I would build a date dimension, but this time it includes an attribute to identify a warehouse, store, etc. With this information, building an SQL statement to find out any kind of delay becomes very easy.

It comes down to finding the first day the warehouse is open (use of MIN(date) where workday='Y') and then it is just counting the number of days between sysdate and the just found date.

 

[Bobje]

To keep it simple, we have three tables: order, holidays and stores

order:
id |Store | OrderDate | scan@WarehouseDate | Scan@StoreDate
1 |123 | 01/01/2007| 02/01/2007 | 04/01/2007

holidays:
Date | Country | Quantity
01/01/2007| Es | 1

Stores
Store |Mo |Tu |We |Th |Fr |Sa |Su
123 |1 |1 |0 |1 |1 |1 |0

1 means store is open that they, 0 means it was closed.

So now I'd like to know whether order 1 was in time at kp.
It was scanned only one day after the order, but as the order was placed on a holiday this is ok, we get a permitted delay of 1 for this.
Then it was scanned only two days later at the store. But 3/1/2007 was a wednesday and the wednesday the store isn't open so we have a permitted delay of 1 for this as well.

Now I need the table delay filled
order delay
orderid | PermDelayHub |PermDelayStore |DelayHub |DelayStore
1 | 1 |1 |1 |2

and this with some (pl/)sql

Thanks

 

[johnherman]

I would create a routine to check the "Days between" two selected dates which also checks the company calendar to ignore (subtract from) selected dates.

Your idea to populate a "1" for a working day and "0" for non-working day would also allow you to sum that value from BeginDate to EndDate.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Bobje]

thanks, that's what I've done in the mean time and performance is ok.