Home » SQL & PL/SQL » SQL & PL/SQL » How to count working day
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to count working day [message #623045 is a reply to message #623035] |
Wed, 03 September 2014 11:44   |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
TO_DATE (LEVEL || '-10-2014', 'DD-MM-YYYY') does:
a) concatenation
b) conversion
DATE '2014-09-30' + LEVEL
does just date arithmetic which in Oracle is built-in functionality.
SQL> DECLARE
2 CURSOR v_cur
3 IS
4 SELECT TO_DATE(LEVEL || '-10-2014','DD-MM-YYYY') DT
5 FROM DUAL
6 CONNECT BY LEVEL <= 31;
7 v_dt DATE;
8 BEGIN
9 FOR v_i IN 1..1000000 LOOP
10 FOR v_rec IN v_cur LOOP
11 v_dt := v_rec.dt;
12 END LOOP;
13 END LOOP;
14 END;
15 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:52.97
SQL> DECLARE
2 CURSOR v_cur
3 IS
4 SELECT DATE '2014-09-30' + LEVEL DT
5 FROM DUAL
6 CONNECT BY LEVEL <= 31;
7 v_dt DATE;
8 BEGIN
9 FOR v_i IN 1..1000000 LOOP
10 FOR v_rec IN v_cur LOOP
11 v_dt := v_rec.dt;
12 END LOOP;
13 END LOOP;
14 END;
15 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:23.77
SQL>
SY.
|
|
|
|
| Re: How to count working day [message #623048 is a reply to message #623027] |
Wed, 03 September 2014 12:14 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 03 September 2014 09:32
Brute force. 
OK, math based solution:
select to_char(dt,'fmMonth, yyyy') dt,
trunc((last_day(dt) - trunc(dt,'iw') + 1) / 7) * 5 +
least(mod(last_day(dt) - trunc(dt,'iw') + 1,7),5) - least(dt - trunc(dt,'iw'),5) work_days
from t
/
For example:
with t as (
select add_months(to_date('01/01/&year','mm/dd/yyyy'),level - 1) dt
from dual
connect by level <= 12
)
select to_char(dt,'fmMonth, yyyy') dt,
trunc((last_day(dt) - trunc(dt,'iw') + 1) / 7) * 5 +
least(mod(last_day(dt) - trunc(dt,'iw') + 1,7),5) - least(dt - trunc(dt,'iw'),5) work_days
from t
/
Enter value for year: 2014
old 2: select add_months(to_date('01/01/&year','mm/dd/yyyy'),level - 1) dt
new 2: select add_months(to_date('01/01/2014','mm/dd/yyyy'),level - 1) dt
DT WORK_DAYS
--------------- ----------
January, 2014 23
February, 2014 20
March, 2014 21
April, 2014 22
May, 2014 22
June, 2014 21
July, 2014 23
August, 2014 21
September, 2014 22
October, 2014 23
November, 2014 20
DT WORK_DAYS
--------------- ----------
December, 2014 23
12 rows selected.
SQL>
And speed comparison:
SQL> DECLARE
2 CURSOR v_cur
3 IS
4 with t as (
5 select add_months(to_date('01/01/2014','mm/dd/yyyy'),level - 1) dt
6 from dual
7 connect by level <= 12
8 )
9 select to_char(trunc(dt,'mm'),'fmMonth, yyyy') dt,
10 count(*) work_days
11 from (
12 select dt + level - 1 dt
13 from t
14 connect by dt = prior dt
15 and prior sys_guid() is not null
16 and dt + level - 1 <= last_day(dt)
17 ) a
18 where to_char(dt,'fmday','nls_date_language = english') not in ('saturday','sunday')
19 group by trunc(dt,'mm');
20 v_work_days NUMBER;
21 BEGIN
22 FOR v_i IN 1..10000 LOOP
23 FOR v_rec IN v_cur LOOP
24 v_work_days := v_rec.work_days;
25 END LOOP;
26 END LOOP;
27 END;
28 /
PL/SQL procedure successfully completed.
Elapsed: 00:02:21.88
SQL> DECLARE
2 CURSOR v_cur
3 IS
4 with t as (
5 select add_months(to_date('01/01/2014','mm/dd/yyyy'),level - 1) dt
6 from dual
7 connect by level <= 12
8 )
9 select to_char(dt,'fmMonth, yyyy') dt,
10 trunc((last_day(dt) - trunc(dt,'iw') + 1) / 7) * 5 +
11 least(mod(last_day(dt) - trunc(dt,'iw') + 1,7),5) - least(dt - trunc(dt,'iw'),5) work_days
12 from t;
13 v_work_days NUMBER;
14 BEGIN
15 FOR v_i IN 1..10000 LOOP
16 FOR v_rec IN v_cur LOOP
17 v_work_days := v_rec.work_days;
18 END LOOP;
19 END LOOP;
20 END;
21 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.07
SQL>
SY.
|
|
|
|
|
|
|
|
| Re: How to count working day [message #631205 is a reply to message #631201] |
Sat, 10 January 2015 05:49 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Sorry, but couldn't understand your question properly. Are you asking how to check if the date is Sunday? Then you could do -
to_char(sysdate, 'DY') = 'SUN'
If you want to check for all the dates in a month or whole year, then use ROW GENERATOR to check.
If this is not the question, then please elaborate.
[Updated on: Sat, 10 January 2015 05:51] Report message to a moderator |
|
|
|
|
|
| Re: How to count working day [message #631208 is a reply to message #631207] |
Sat, 10 January 2015 05:56 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Use the same TO_CHAR. DY format model gives the abbreviated form of the days, DAY gives full name, but blank padded. Use whatever is appropriate for you.
Ok, I see you have edited your post.
You could do -
DECODE(to_char(dt_col, 'DY'), 'SUN', to_char(dt_col, 'DD-MM-YYYY')||' SUNDAY', to_char(dt_col, 'DD-MM-YYYY'))
You could also do it using CASE expression.
[Updated on: Sat, 10 January 2015 06:01] Report message to a moderator |
|
|
|
| Re: How to count working day [message #631213 is a reply to message #631208] |
Sat, 10 January 2015 07:46 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
All that you posted is NLS dependent:
SQL> select to_char(sysdate,'DY') from dual; -- NLS dependent
TO_
---
SAT
SQL> alter session set nls_language=spanish;
Session altered.
SQL> select to_char(sysdate,'DY') from dual; -- NLS dependent
TO_
---
SAB
SQL> alter session set nls_language=swedish;
Session altered.
SQL> select to_char(sysdate,'DY') from dual; -- NLS dependent
TO
--
LO
SQL> select to_char(sysdate,'DY','nls_date_language=english') from dual; -- NLS independent
TO_
---
SAT
SQL>
SY.
|
|
|
|
| Re: How to count working day [message #631219 is a reply to message #631213] |
Sat, 10 January 2015 09:17 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Thanks, I missed it.
DECODE(to_char(dt_col, 'DY','nls_date_language = english'), 'SUN', to_char(dt_col, 'DD-MM-YYYY')||' SUNDAY', to_char(dt_col, 'DD-MM-YYYY'))
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 00:16:47 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
