Home » SQL & PL/SQL » SQL & PL/SQL » how to calculate work time or lead time except weekends (merged)
how to calculate work time or lead time except weekends (merged) [message #390021] Wed, 04 March 2009 11:13 Go to next message
kang
Messages: 89
Registered: November 2007
Member
how to calculate work time or lead time except weekends?

for example,
2009.02.28 18:00 to 2009.03.02 19:00 -> 19hr because 2009.02.28 and 2009.03.01 are weekends.

2009.03.03 13:10 to 2009.03.04 14:15 -> 25hr 5minutes

etc.

Re: how to calculate work time or lead time except weekends (merged) [message #390029 is a reply to message #390021] Wed, 04 March 2009 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: how to calculate work time or lead time except weekends (merged) [message #390031 is a reply to message #390021] Wed, 04 March 2009 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
This has been asked & answered here 1 or more times.
Re: how to calculate work time or lead time except weekends (merged) [message #390073 is a reply to message #390031] Wed, 04 March 2009 18:32 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
I can't see any.
Will you do me a favor?
Re: how to calculate work time or lead time except weekends (merged) [message #390075 is a reply to message #390021] Wed, 04 March 2009 18:41 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
SEARCH for "exclude weekend"
Re: how to calculate work time or lead time except weekends (merged) [message #390098 is a reply to message #390073] Wed, 04 March 2009 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kang wrote on Thu, 05 March 2009 01:32
I can't see any.
Will you do me a favor?

Will you do us a favor?
Michel Cadot wrote on Wed, 04 March 2009 18:54
Please read OraFAQ Forum Guide...
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel


Re: how to calculate work time or lead time except weekends (merged) [message #390110 is a reply to message #390021] Thu, 05 March 2009 00:29 Go to previous messageGo to next message
saini006
Messages: 8
Registered: July 2008
Location: hyderabad
Junior Member
i think it will work


SET SERVEROUTPUT ON
declare
LED DATE; -- start date
LD DATE; -- end date
TOT NUMBER;
DA VARCHAR2(10);
begin
TOT:=LED-LD;
DBMS_OUTPUT.PUT_LINE (TOT);

LOOP
EXIT WHEN LED=LD;
LD:=LD+1;
DA:= TRIM(TO_CHAR(LD,'DAY'));
DBMS_OUTPUT.PUT_LINE (DA);
DA:= TRIM(TO_CHAR(LD,'DAY'));
IF DA='SATURDAY' OR DA='SUNDAY' THEN
TOT:=TOT-1;
END IF;
END LOOP;
TOT:=TOT*24
DBMS_OUTPUT.PUT_LINE (TOT);
END;

Re: how to calculate work time or lead time except weekends (merged) [message #390113 is a reply to message #390110] Thu, 05 March 2009 00:48 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you check your code against the example OP gave? Obviously, no.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Select "FOR UPDATE"
Next Topic: ROWNUMBER () OVER Partition in Oracle 9i
Goto Forum:
  


Current Time: Fri Dec 09 11:41:44 CST 2016

Total time taken to generate the page: 0.12626 seconds