| SQL HELP PLEEZ! [message #372273] |
Wed, 24 January 2001 12:06  |
niki
Messages: 2
Registered: January 2001
|
Junior Member |
|
|
|
I am trying to calculate a time. I need to create an interval time that is calculated from closed - open but I need to take out weekend time. Is there a SQL stmt that will calc at the query level??
|
|
|
|
| Re: SQL HELP PLEEZ! [message #372283 is a reply to message #372273] |
Thu, 25 January 2001 14:04  |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
Here is a function that will perform what you want. After you have created the function perform the following query.
SELECT TIME_BETWEEN(Your open date column, Your close date column)
FROM Your table name
WHERE ....
(the results will be returned in days. if you want hours multiply results by 24, minutes by 1440)
CREATE OR REPLACE FUNCTION TIME_BETWEEN(
P_OPEN_DATE IN DATE
,P_CLOSE_DATE IN DATE
) RETURN NUMBER AS
--
V_CNT NUMBER;
--
BEGIN
--
SELECT (P_CLOSE_DATE - P_OPEN_DATE) - A.WKEND_CNT
INTO V_CNT
FROM (SELECT COUNT(*) WKEND_CNT
FROM (SELECT ROWNUM RNUM
FROM ALL_OBJECTS
WHERE ROWNUM <= TRUNC(P_CLOSE_DATE) - TRUNC(P_OPEN_DATE))
WHERE TO_CHAR(P_OPEN_DATE + RNUM -1, 'DY' ) IN ('SAT', 'SUN')) A ;
--
RETURN V_CNT;
--
END TIME_BETWEEN;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
