| DECODE Assistance [message #6849] |
Thu, 08 May 2003 08:28  |
Sarah Harvey
Messages: 7
Registered: April 2003
|
Junior Member |
|
|
Sorry am being a real newbie on this one!
I have been asked to report back the number of orders delivered that were delivered <= to a certain time the next day provided they were despatched the day before ! This is currently 0700am but the users would ideally like this to be an input field.
I therefore need to do some kind of DECODE statement that says if the despatch date is -1 the pod date and the time is <= the time entered then 1, 0. I am struggling however because this DECODE statement surely needs a SIGN function and a CONVERT fuction to extract the time ?!
Confused? Me too!
|
|
|
|
|
|
| Re: DECODE Assistance [message #6867 is a reply to message #6849] |
Fri, 09 May 2003 01:10  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It sounds like you are overly complicating things. I don't see a need for decode or sign. You can truncate the dispatch and delivery dates to eliminate the time portion and subtract the dispatch date from the delivery date to see if the result is one (dispatched the day before). You can use to_char to select only the time portion of the dispatch date for comparison to 0700 or whatever. You can use a substitution variable so they can enter 0700 or whatever time. See example below.
SELECT COUNT (*)
FROM order_table
WHERE TRUNC (delivered) - TRUNC (dispatched) = 1
AND TO_CHAR (dispatched, 'HH24MM') <= '&time_as_hhmm'
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
