Home » SQL & PL/SQL » SQL & PL/SQL » delay calculation
delay calculation [message #249178] Tue, 03 July 2007 10:26 Go to next message
KoalaBIar
Messages: 5
Registered: April 2007
Location: Belgium
Junior Member
Hello,
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?
Re: delay calculation [message #249191 is a reply to message #249178] Tue, 03 July 2007 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Now post a test case (create table, insert statements) and the result you want for this test case.

Regards
Michel
Re: delay calculation [message #249704 is a reply to message #249178] Thu, 05 July 2007 10:43 Go to previous messageGo to next message
KoalaBIar
Messages: 5
Registered: April 2007
Location: Belgium
Junior Member
Oracle version: 10.2.0.3

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 , 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

[Updated on: Thu, 05 July 2007 11:28]

Report message to a moderator

Re: delay calculation [message #249708 is a reply to message #249704] Thu, 05 July 2007 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't even read your post until it is formated.

Regards
Michel
Re: delay calculation [message #249714 is a reply to message #249708] Thu, 05 July 2007 11:30 Go to previous messageGo to next message
KoalaBIar
Messages: 5
Registered: April 2007
Location: Belgium
Junior Member
Dude, check the source of my message there lot's of spaces everywhere as I tried to format it but this seems not to be working.
Hopefully others will read it!
Re: delay calculation [message #249715 is a reply to message #249178] Thu, 05 July 2007 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Hopefully others will read it!
I did & agree with Michel.
Re: delay calculation [message #249732 is a reply to message #249714] Thu, 05 July 2007 12:34 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
KoalaBIar wrote on Thu, 05 July 2007 12:30
Dude, check the source of my message there lot's of spaces everywhere as I tried to format it but this seems not to be working.
Hopefully others will read it!


There is a link right in Michel's post on how to format your posts. It's very clear. And why don't you just start up SQL*Plus and cut & paste rather than type the whole thing over again which is ripe for typing errors.

[Updated on: Thu, 05 July 2007 12:35]

Report message to a moderator

Previous Topic: Large query with multiple conditional joins
Next Topic: to check user logged in to apps
Goto Forum:
  


Current Time: Thu Dec 08 16:38:56 CST 2016

Total time taken to generate the page: 0.15006 seconds