Home » SQL & PL/SQL » SQL & PL/SQL » Need help to find Business day number of the month (merged)
Need help to find Business day number of the month (merged) [message #343244] Tue, 26 August 2008 14:26 Go to next message
sheker2007
Messages: 19
Registered: November 2007
Junior Member
I need help on business day number of the month.
For example AUg,2008.I need 3 columns.
1)DT 2)DAY_NAME 3)business day number of the month(Need to exclude weekend dates)

For example (August Month):
DT DAY_NAME Business Day Number of the Month
---------- -------- ---------------------------------
08/01/2008 FRIDAY 1
08/02/2008 SATURDAY 0
08/03/2008 SUNDAY 0
08/04/2008 MONDAY 2
08/05/2008 TUESEDAY 3
08/06/2008 WEDNUSEDAY 4
08/07/2008 THURSDAY 5
08/08/2008 FRIDAY 6
----
08/29/2008 FRIDAY 21
-------
08/31/2008 SUNDAY 0

Below Code working for two columns but business day number of the month not getting.When i pass the year it will populate for entire year for all months.Working fine for only 2 columns.

CREATE OR REPLACE PROCEDURE AFSDBOD.STP_DT_DIM_CPNEW
( inp_CALENDER_YR IN NUMBER)
AS
v_YRMON VARCHAR2(6) := NULL;
BEGIN
/** ENABLE DBMS_OUTPUT Function **/
DBMS_OUTPUT.ENABLE;

FOR i IN 1..12 LOOP

SELECT TO_CHAR(TO_DATE(TO_CHAR(inp_CALENDER_YR)||TO_CHAR(i),'YYYYMM'),'YYYYMM') INTO v_YRMON FROM DUAL;

INSERT INTO DT_TMP
select
TO_DATE(TO_CHAR((TO_DATE(v_YRMON,'YYYYMM')-1 + LEVEL), 'YYYYMMDD'), 'YYYYMMDD') GNRC_DT,
TRIM(TO_CHAR((TO_DATE(v_YRMON,'YYYYMM')-1 + LEVEL), 'DAY')) DAY_NAME,
(Case when TRIM(TO_CHAR((TO_DATE(v_YRMON,'YYYYMM')-1 + LEVEL), 'DAY')) in ('SUNDAY','SATURDAY') then 0 else LEVEL end) business_day_number_of_the_month
FROM DUAL
WHERE (TO_DATE(v_YRMON,'YYYYMM')-1+LEVEL) <= LAST_DAY(TO_DATE(v_YRMON,'YYYYMM'))
CONNECT BY LEVEL <= 31;
END LOOP;

END STP_DT_DIM_CPNEW;


Regards
Sheker
Re: Need help on business day number of the month [message #343248 is a reply to message #343244] Tue, 26 August 2008 14:39 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

please properly format your post as stated in URL above
Re: Need help on business day number of the month [message #343278 is a reply to message #343244] Tue, 26 August 2008 17:46 Go to previous messageGo to next message
prtz
Messages: 11
Registered: January 2008
Junior Member
Quote:

Below Code working for two columns but business day number of the month not getting.When i pass the year it will populate for entire year for all months.Working fine for only 2 columns.



Assuming that you want to show all workdays for a year -

--
-- Show all business days for the current year
--

--
select trunc(sysdate,'yyyy') + level - 1 as dt,
       to_char(trunc(sysdate,'yyyy') + level - 1,'DAY') as day_name,
       case when to_char(trunc(sysdate,'yyyy') + level - 1,'Dy') in ('Sat','Sun')
            then 0
            else level - sum(case when to_char(trunc(sysdate,'yyyy') + level - 1,'Dy') in ('Sat','Sun')
                                  then 1
                                  else 0
                             end) over (order by to_char(trunc(sysdate,'yyyy') + level - 1,'DDD'))
       end as bday
from dual
connect by level + trunc(sysdate,'yyyy') <= trunc(add_months(sysdate,12),'yyyy');


--
-- Show all business days for a given year
--

--
select to_date('0101'||&yr,'mmddyyyy') + level - 1 as dt,
       to_char(to_date('0101'||&yr,'mmddyyyy') + level - 1,'DAY') as day_name,
       case when to_char(to_date('0101'||&yr,'mmddyyyy') + level - 1,'Dy') in ('Sat','Sun')
            then 0
            else level - sum(case when to_char(to_date('0101'||&yr,'mmddyyyy') + level - 1,'Dy') in ('Sat','Sun')
                                  then 1
                                  else 0
                             end) over (order by to_char(to_date('0101'||&yr,'mmddyyyy') + level - 1,'DDD'))
       end as bday
from dual
connect by level + to_date('0101'||&yr,'mmddyyyy') <= trunc(add_months(to_date('0101'||&yr,'mmddyyyy'),12),'yyyy');


You can fix these to display the workdays for a given month.

HTH
prtz

Execution of the 1st query is shown below:

test@XE> --
test@XE> select trunc(sysdate,'yyyy') + level - 1 as dt,
  2         to_char(trunc(sysdate,'yyyy') + level - 1,'DAY') as day_name,
  3         case when to_char(trunc(sysdate,'yyyy') + level - 1,'Dy') in ('Sat','Sun')
  4              then 0
  5              else level - sum(case when to_char(trunc(sysdate,'yyyy') + level - 1,'Dy') in ('Sat','Sun')
  6                                    then 1
  7                                    else 0
  8                               end) over (order by to_char(trunc(sysdate,'yyyy') + level - 1,'DDD'))
  9         end as bday
 10  from dual
 11  connect by level + trunc(sysdate,'yyyy') <= trunc(add_months(sysdate,12),'yyyy');

DT                 DAY_NAME                                   BDAY
------------------ ------------------------------------ ----------
01-JAN-08          TUESDAY                                       1
02-JAN-08          WEDNESDAY                                     2
03-JAN-08          THURSDAY                                      3
04-JAN-08          FRIDAY                                        4
05-JAN-08          SATURDAY                                      0
06-JAN-08          SUNDAY                                        0
07-JAN-08          MONDAY                                        5
08-JAN-08          TUESDAY                                       6
09-JAN-08          WEDNESDAY                                     7
10-JAN-08          THURSDAY                                      8
11-JAN-08          FRIDAY                                        9
12-JAN-08          SATURDAY                                      0
13-JAN-08          SUNDAY                                        0
14-JAN-08          MONDAY                                       10
15-JAN-08          TUESDAY                                      11
16-JAN-08          WEDNESDAY                                    12
17-JAN-08          THURSDAY                                     13
18-JAN-08          FRIDAY                                       14
19-JAN-08          SATURDAY                                      0
20-JAN-08          SUNDAY                                        0
21-JAN-08          MONDAY                                       15
22-JAN-08          TUESDAY                                      16
23-JAN-08          WEDNESDAY                                    17
24-JAN-08          THURSDAY                                     18
25-JAN-08          FRIDAY                                       19
26-JAN-08          SATURDAY                                      0
27-JAN-08          SUNDAY                                        0
28-JAN-08          MONDAY                                       20
29-JAN-08          TUESDAY                                      21
30-JAN-08          WEDNESDAY                                    22
31-JAN-08          THURSDAY                                     23
01-FEB-08          FRIDAY                                       24
02-FEB-08          SATURDAY                                      0
03-FEB-08          SUNDAY                                        0
04-FEB-08          MONDAY                                       25
05-FEB-08          TUESDAY                                      26
06-FEB-08          WEDNESDAY                                    27
07-FEB-08          THURSDAY                                     28
08-FEB-08          FRIDAY                                       29
09-FEB-08          SATURDAY                                      0
10-FEB-08          SUNDAY                                        0
11-FEB-08          MONDAY                                       30
12-FEB-08          TUESDAY                                      31
13-FEB-08          WEDNESDAY                                    32
14-FEB-08          THURSDAY                                     33
15-FEB-08          FRIDAY                                       34
16-FEB-08          SATURDAY                                      0
17-FEB-08          SUNDAY                                        0
18-FEB-08          MONDAY                                       35
19-FEB-08          TUESDAY                                      36
20-FEB-08          WEDNESDAY                                    37
21-FEB-08          THURSDAY                                     38
22-FEB-08          FRIDAY                                       39
23-FEB-08          SATURDAY                                      0
24-FEB-08          SUNDAY                                        0
25-FEB-08          MONDAY                                       40
26-FEB-08          TUESDAY                                      41
27-FEB-08          WEDNESDAY                                    42
28-FEB-08          THURSDAY                                     43
29-FEB-08          FRIDAY                                       44
01-MAR-08          SATURDAY                                      0
02-MAR-08          SUNDAY                                        0
03-MAR-08          MONDAY                                       45
04-MAR-08          TUESDAY                                      46
05-MAR-08          WEDNESDAY                                    47
06-MAR-08          THURSDAY                                     48
07-MAR-08          FRIDAY                                       49
08-MAR-08          SATURDAY                                      0
09-MAR-08          SUNDAY                                        0
10-MAR-08          MONDAY                                       50
11-MAR-08          TUESDAY                                      51
12-MAR-08          WEDNESDAY                                    52
13-MAR-08          THURSDAY                                     53
14-MAR-08          FRIDAY                                       54
15-MAR-08          SATURDAY                                      0
16-MAR-08          SUNDAY                                        0
17-MAR-08          MONDAY                                       55
18-MAR-08          TUESDAY                                      56
19-MAR-08          WEDNESDAY                                    57
20-MAR-08          THURSDAY                                     58
21-MAR-08          FRIDAY                                       59
22-MAR-08          SATURDAY                                      0
23-MAR-08          SUNDAY                                        0
24-MAR-08          MONDAY                                       60
25-MAR-08          TUESDAY                                      61
26-MAR-08          WEDNESDAY                                    62
27-MAR-08          THURSDAY                                     63
28-MAR-08          FRIDAY                                       64
29-MAR-08          SATURDAY                                      0
30-MAR-08          SUNDAY                                        0
31-MAR-08          MONDAY                                       65
01-APR-08          TUESDAY                                      66
02-APR-08          WEDNESDAY                                    67
03-APR-08          THURSDAY                                     68
04-APR-08          FRIDAY                                       69
05-APR-08          SATURDAY                                      0
06-APR-08          SUNDAY                                        0
07-APR-08          MONDAY                                       70
08-APR-08          TUESDAY                                      71
09-APR-08          WEDNESDAY                                    72
10-APR-08          THURSDAY                                     73
11-APR-08          FRIDAY                                       74
12-APR-08          SATURDAY                                      0
13-APR-08          SUNDAY                                        0
14-APR-08          MONDAY                                       75
15-APR-08          TUESDAY                                      76
16-APR-08          WEDNESDAY                                    77
17-APR-08          THURSDAY                                     78
18-APR-08          FRIDAY                                       79
19-APR-08          SATURDAY                                      0
20-APR-08          SUNDAY                                        0
21-APR-08          MONDAY                                       80
22-APR-08          TUESDAY                                      81
23-APR-08          WEDNESDAY                                    82
24-APR-08          THURSDAY                                     83
25-APR-08          FRIDAY                                       84
26-APR-08          SATURDAY                                      0
27-APR-08          SUNDAY                                        0
28-APR-08          MONDAY                                       85
29-APR-08          TUESDAY                                      86
30-APR-08          WEDNESDAY                                    87
01-MAY-08          THURSDAY                                     88
02-MAY-08          FRIDAY                                       89
03-MAY-08          SATURDAY                                      0
04-MAY-08          SUNDAY                                        0
05-MAY-08          MONDAY                                       90
06-MAY-08          TUESDAY                                      91
07-MAY-08          WEDNESDAY                                    92
08-MAY-08          THURSDAY                                     93
09-MAY-08          FRIDAY                                       94
10-MAY-08          SATURDAY                                      0
11-MAY-08          SUNDAY                                        0
12-MAY-08          MONDAY                                       95
13-MAY-08          TUESDAY                                      96
14-MAY-08          WEDNESDAY                                    97
15-MAY-08          THURSDAY                                     98
16-MAY-08          FRIDAY                                       99
17-MAY-08          SATURDAY                                      0
18-MAY-08          SUNDAY                                        0
19-MAY-08          MONDAY                                      100
20-MAY-08          TUESDAY                                     101
21-MAY-08          WEDNESDAY                                   102
22-MAY-08          THURSDAY                                    103
23-MAY-08          FRIDAY                                      104
24-MAY-08          SATURDAY                                      0
25-MAY-08          SUNDAY                                        0
26-MAY-08          MONDAY                                      105
27-MAY-08          TUESDAY                                     106
28-MAY-08          WEDNESDAY                                   107
29-MAY-08          THURSDAY                                    108
30-MAY-08          FRIDAY                                      109
31-MAY-08          SATURDAY                                      0
01-JUN-08          SUNDAY                                        0
02-JUN-08          MONDAY                                      110
03-JUN-08          TUESDAY                                     111
04-JUN-08          WEDNESDAY                                   112
05-JUN-08          THURSDAY                                    113
06-JUN-08          FRIDAY                                      114
07-JUN-08          SATURDAY                                      0
08-JUN-08          SUNDAY                                        0
09-JUN-08          MONDAY                                      115
10-JUN-08          TUESDAY                                     116
11-JUN-08          WEDNESDAY                                   117
12-JUN-08          THURSDAY                                    118
13-JUN-08          FRIDAY                                      119
14-JUN-08          SATURDAY                                      0
15-JUN-08          SUNDAY                                        0
16-JUN-08          MONDAY                                      120
17-JUN-08          TUESDAY                                     121
18-JUN-08          WEDNESDAY                                   122
19-JUN-08          THURSDAY                                    123
20-JUN-08          FRIDAY                                      124
21-JUN-08          SATURDAY                                      0
22-JUN-08          SUNDAY                                        0
23-JUN-08          MONDAY                                      125
24-JUN-08          TUESDAY                                     126
25-JUN-08          WEDNESDAY                                   127
26-JUN-08          THURSDAY                                    128
27-JUN-08          FRIDAY                                      129
28-JUN-08          SATURDAY                                      0
29-JUN-08          SUNDAY                                        0
30-JUN-08          MONDAY                                      130
01-JUL-08          TUESDAY                                     131
02-JUL-08          WEDNESDAY                                   132
03-JUL-08          THURSDAY                                    133
04-JUL-08          FRIDAY                                      134
05-JUL-08          SATURDAY                                      0
06-JUL-08          SUNDAY                                        0
07-JUL-08          MONDAY                                      135
08-JUL-08          TUESDAY                                     136
09-JUL-08          WEDNESDAY                                   137
10-JUL-08          THURSDAY                                    138
11-JUL-08          FRIDAY                                      139
12-JUL-08          SATURDAY                                      0
13-JUL-08          SUNDAY                                        0
14-JUL-08          MONDAY                                      140
15-JUL-08          TUESDAY                                     141
16-JUL-08          WEDNESDAY                                   142
17-JUL-08          THURSDAY                                    143
18-JUL-08          FRIDAY                                      144
19-JUL-08          SATURDAY                                      0
20-JUL-08          SUNDAY                                        0
21-JUL-08          MONDAY                                      145
22-JUL-08          TUESDAY                                     146
23-JUL-08          WEDNESDAY                                   147
24-JUL-08          THURSDAY                                    148
25-JUL-08          FRIDAY                                      149
26-JUL-08          SATURDAY                                      0
27-JUL-08          SUNDAY                                        0
28-JUL-08          MONDAY                                      150
29-JUL-08          TUESDAY                                     151
30-JUL-08          WEDNESDAY                                   152
31-JUL-08          THURSDAY                                    153
01-AUG-08          FRIDAY                                      154
02-AUG-08          SATURDAY                                      0
03-AUG-08          SUNDAY                                        0
04-AUG-08          MONDAY                                      155
05-AUG-08          TUESDAY                                     156
06-AUG-08          WEDNESDAY                                   157
07-AUG-08          THURSDAY                                    158
08-AUG-08          FRIDAY                                      159
09-AUG-08          SATURDAY                                      0
10-AUG-08          SUNDAY                                        0
11-AUG-08          MONDAY                                      160
12-AUG-08          TUESDAY                                     161
13-AUG-08          WEDNESDAY                                   162
14-AUG-08          THURSDAY                                    163
15-AUG-08          FRIDAY                                      164
16-AUG-08          SATURDAY                                      0
17-AUG-08          SUNDAY                                        0
18-AUG-08          MONDAY                                      165
19-AUG-08          TUESDAY                                     166
20-AUG-08          WEDNESDAY                                   167
21-AUG-08          THURSDAY                                    168
22-AUG-08          FRIDAY                                      169
23-AUG-08          SATURDAY                                      0
24-AUG-08          SUNDAY                                        0
25-AUG-08          MONDAY                                      170
26-AUG-08          TUESDAY                                     171
27-AUG-08          WEDNESDAY                                   172
28-AUG-08          THURSDAY                                    173
29-AUG-08          FRIDAY                                      174
30-AUG-08          SATURDAY                                      0
31-AUG-08          SUNDAY                                        0
01-SEP-08          MONDAY                                      175
02-SEP-08          TUESDAY                                     176
03-SEP-08          WEDNESDAY                                   177
04-SEP-08          THURSDAY                                    178
05-SEP-08          FRIDAY                                      179
06-SEP-08          SATURDAY                                      0
07-SEP-08          SUNDAY                                        0
08-SEP-08          MONDAY                                      180
09-SEP-08          TUESDAY                                     181
10-SEP-08          WEDNESDAY                                   182
11-SEP-08          THURSDAY                                    183
12-SEP-08          FRIDAY                                      184
13-SEP-08          SATURDAY                                      0
14-SEP-08          SUNDAY                                        0
15-SEP-08          MONDAY                                      185
16-SEP-08          TUESDAY                                     186
17-SEP-08          WEDNESDAY                                   187
18-SEP-08          THURSDAY                                    188
19-SEP-08          FRIDAY                                      189
20-SEP-08          SATURDAY                                      0
21-SEP-08          SUNDAY                                        0
22-SEP-08          MONDAY                                      190
23-SEP-08          TUESDAY                                     191
24-SEP-08          WEDNESDAY                                   192
25-SEP-08          THURSDAY                                    193
26-SEP-08          FRIDAY                                      194
27-SEP-08          SATURDAY                                      0
28-SEP-08          SUNDAY                                        0
29-SEP-08          MONDAY                                      195
30-SEP-08          TUESDAY                                     196
01-OCT-08          WEDNESDAY                                   197
02-OCT-08          THURSDAY                                    198
03-OCT-08          FRIDAY                                      199
04-OCT-08          SATURDAY                                      0
05-OCT-08          SUNDAY                                        0
06-OCT-08          MONDAY                                      200
07-OCT-08          TUESDAY                                     201
08-OCT-08          WEDNESDAY                                   202
09-OCT-08          THURSDAY                                    203
10-OCT-08          FRIDAY                                      204
11-OCT-08          SATURDAY                                      0
12-OCT-08          SUNDAY                                        0
13-OCT-08          MONDAY                                      205
14-OCT-08          TUESDAY                                     206
15-OCT-08          WEDNESDAY                                   207
16-OCT-08          THURSDAY                                    208
17-OCT-08          FRIDAY                                      209
18-OCT-08          SATURDAY                                      0
19-OCT-08          SUNDAY                                        0
20-OCT-08          MONDAY                                      210
21-OCT-08          TUESDAY                                     211
22-OCT-08          WEDNESDAY                                   212
23-OCT-08          THURSDAY                                    213
24-OCT-08          FRIDAY                                      214
25-OCT-08          SATURDAY                                      0
26-OCT-08          SUNDAY                                        0
27-OCT-08          MONDAY                                      215
28-OCT-08          TUESDAY                                     216
29-OCT-08          WEDNESDAY                                   217
30-OCT-08          THURSDAY                                    218
31-OCT-08          FRIDAY                                      219
01-NOV-08          SATURDAY                                      0
02-NOV-08          SUNDAY                                        0
03-NOV-08          MONDAY                                      220
04-NOV-08          TUESDAY                                     221
05-NOV-08          WEDNESDAY                                   222
06-NOV-08          THURSDAY                                    223
07-NOV-08          FRIDAY                                      224
08-NOV-08          SATURDAY                                      0
09-NOV-08          SUNDAY                                        0
10-NOV-08          MONDAY                                      225
11-NOV-08          TUESDAY                                     226
12-NOV-08          WEDNESDAY                                   227
13-NOV-08          THURSDAY                                    228
14-NOV-08          FRIDAY                                      229
15-NOV-08          SATURDAY                                      0
16-NOV-08          SUNDAY                                        0
17-NOV-08          MONDAY                                      230
18-NOV-08          TUESDAY                                     231
19-NOV-08          WEDNESDAY                                   232
20-NOV-08          THURSDAY                                    233
21-NOV-08          FRIDAY                                      234
22-NOV-08          SATURDAY                                      0
23-NOV-08          SUNDAY                                        0
24-NOV-08          MONDAY                                      235
25-NOV-08          TUESDAY                                     236
26-NOV-08          WEDNESDAY                                   237
27-NOV-08          THURSDAY                                    238
28-NOV-08          FRIDAY                                      239
29-NOV-08          SATURDAY                                      0
30-NOV-08          SUNDAY                                        0
01-DEC-08          MONDAY                                      240
02-DEC-08          TUESDAY                                     241
03-DEC-08          WEDNESDAY                                   242
04-DEC-08          THURSDAY                                    243
05-DEC-08          FRIDAY                                      244
06-DEC-08          SATURDAY                                      0
07-DEC-08          SUNDAY                                        0
08-DEC-08          MONDAY                                      245
09-DEC-08          TUESDAY                                     246
10-DEC-08          WEDNESDAY                                   247
11-DEC-08          THURSDAY                                    248
12-DEC-08          FRIDAY                                      249
13-DEC-08          SATURDAY                                      0
14-DEC-08          SUNDAY                                        0
15-DEC-08          MONDAY                                      250
16-DEC-08          TUESDAY                                     251
17-DEC-08          WEDNESDAY                                   252
18-DEC-08          THURSDAY                                    253
19-DEC-08          FRIDAY                                      254
20-DEC-08          SATURDAY                                      0
21-DEC-08          SUNDAY                                        0
22-DEC-08          MONDAY                                      255
23-DEC-08          TUESDAY                                     256
24-DEC-08          WEDNESDAY                                   257
25-DEC-08          THURSDAY                                    258
26-DEC-08          FRIDAY                                      259
27-DEC-08          SATURDAY                                      0
28-DEC-08          SUNDAY                                        0
29-DEC-08          MONDAY                                      260
30-DEC-08          TUESDAY                                     261
31-DEC-08          WEDNESDAY                                   262

366 rows selected.

test@XE>
test@XE>

Re: Need help on business day number of the month [message #343282 is a reply to message #343244] Tue, 26 August 2008 18:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I want to say there was a time years ago with Oracle8i wherein a user could define their own calendars in the database and then use them.

But for the life of me I can't find the documentation anywhere for any current release.

Maybe someone else remembers?

Kevin
Re: Need help on business day number of the month [message #343339 is a reply to message #343244] Wed, 27 August 2008 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at AskTom thread: Counting the number of business days between 2 dates

Regards
Michel
Re: Need help on business day number of the month [message #343341 is a reply to message #343282] Wed, 27 August 2008 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kevin,

Maybe you're thinking about NLS Calendar Utility.

Regards
Michel

Re: Need help on business day number of the month [message #343502 is a reply to message #343341] Wed, 27 August 2008 07:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Maybe but I don't think so.

I thought there was something in the database that allowed you to define a calendar (not a gui tool). Maybe it is just my imagination.

Kevin
Re: Need help on business day number of the month [message #360127 is a reply to message #343278] Wed, 19 November 2008 12:54 Go to previous messageGo to next message
prem1
Messages: 5
Registered: November 2008
Junior Member
I have same requirement but using another table for holiday lookup.I have two tables Calender and holiday will populated yearly.
caldener table columns :
Calender_Date
business_Day_No_Of_Month

Holiday table Columns :
Calender_Date
Holiday_Indicator

Holiday table will have only holiday dates (10 rows per year).
Now i want to update calender table for business_Day_No_Of_Month
values using holiday indicator values(yes/no) on holiday table.

sample data for business day of month :

08/01/2008 FRIDAY 1
08/02/2008 SATURDAY 0
08/03/2008 SUNDAY 0
08/04/2008 MONDAY 2
08/05/2008 TUESEDAY 3
08/06/2008 WEDNUSEDAY 4
08/07/2008 THURSDAY 5
08/08/2008 FRIDAY 6
----
08/29/2008 FRIDAY 21
-------
08/31/2008 SUNDAY 0
---------------------------
same way for September,.....


need help i am new for this forum

Need help to find Business day number of the month [message #360375 is a reply to message #343244] Thu, 20 November 2008 13:56 Go to previous messageGo to next message
prem1
Messages: 5
Registered: November 2008
Junior Member
Hi All,

This some one already posted but i tried that way i did not get output.I need business day number for month after excluding holidays and weekends.Holidays information getting form lookup table and SAT,SUN values hard coded in SQL.
Need help on this...

I need output like below :
CALENDER DATE Busines_day_no_of_month
------------------------- ----------------------
11/1/2008 12:00:00.000 AM 0
11/2/2008 12:00:00.000 AM 0
11/3/2008 12:00:00.000 AM 1
11/4/2008 12:00:00.000 AM 2
11/5/2008 12:00:00.000 AM 3
11/6/2008 12:00:00.000 AM 4
11/7/2008 12:00:00.000 AM 5
11/8/2008 12:00:00.000 AM 0
11/9/2008 12:00:00.000 AM 0
11/10/2008 12:00:00.000 AM 6
11/11/2008 12:00:00.000 AM 0
11/12/2008 12:00:00.000 AM 7
11/13/2008 12:00:00.000 AM 8
11/14/2008 12:00:00.000 AM 9
11/15/2008 12:00:00.000 AM 0
11/16/2008 12:00:00.000 AM 0
11/17/2008 12:00:00.000 AM 10
11/18/2008 12:00:00.000 AM 11
11/19/2008 12:00:00.000 AM 12
11/20/2008 12:00:00.000 AM 13
11/21/2008 12:00:00.000 AM 14
11/22/2008 12:00:00.000 AM 0
11/23/2008 12:00:00.000 AM 0
11/24/2008 12:00:00.000 AM 15
11/25/2008 12:00:00.000 AM 16
11/26/2008 12:00:00.000 AM 17
11/27/2008 12:00:00.000 AM 0
11/28/2008 12:00:00.000 AM 0
11/29/2008 12:00:00.000 AM 18
11/30/2008 12:00:00.000 AM 19

Here is SQL i am trying :
select
TO_DATE(TO_CHAR((TO_DATE(200808,'YYYYMM')-1 + LEVEL), 'YYYYMMDD'), 'YYYYMMDD') CALNDR_DT,
case when TO_DATE(TO_CHAR((TO_DATE(200808,'YYYYMM')-1 + LEVEL), 'YYYYMMDD'), 'YYYYMMDD') in (select F.HLDY_DT from HLDY F where F.HLDY_IND=1
and F.OBSVD_HLDY_IND=1
and to_char(F.HLDY_DT,'YYYYMM')='200808')
OR to_char(to_date(200808,'YYYYMM') + level - 1,'Dy') in ('Sat','Sun')
then 0
else level - sum(case when TO_DATE(TO_CHAR((TO_DATE(200808,'YYYYMM')-1 + LEVEL), 'YYYYMMDD'), 'YYYYMMDD') in (select F.HLDY_DT from HLDY F where F.HLDY_IND=1
and F.OBSVD_HLDY_IND=1
and to_char(F.HLDY_DT,'YYYYMM')='200808')
OR to_char(to_date(200808,'YYYYMM') + level - 1,'Dy') in ('Sat','Sun')
then 1
else 0
end) over (order by to_char(to_date(200808,'YYYYMM') + level - 1,'DDD'))
end as busines_day_no_of_month
from dual
WHERE (TO_DATE(200808,'YYYYMM')-1+LEVEL) <= LAST_DAY(TO_DATE(200808,'YYYYMM'))
CONNECT BY LEVEL <= 31;

Thanks in Advance,
Prem

[Updated on: Thu, 20 November 2008 14:00]

Report message to a moderator

Re: Need help to find Business day number of the month [message #360377 is a reply to message #360375] Thu, 20 November 2008 14:20 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Prem1,

Please read OraFAQ Forum Guide especially on "How to Format Your Post?"
Give a complete description of the problem. You have mentioned about a lookup table. Describe it. Posting a test case with the desired results will help people understand your requirement better.

[***Added]
Didn't notice the thread being moved.

I am a bit confused with your output. You have 0 for all holidays (inclusive of Saturdays, Sundays and the other hoiday mentioned in your lookup table) in "Busines_day_no_of_month" column of your desired result. Then why is the last Saturday and Sunday (the one after the Thanksgiving) considered as a working day? Is it a typo? Please explain

Regards,
Jo

[Updated on: Thu, 20 November 2008 14:30]

Report message to a moderator

Re: Need help to find Business day number of the month [message #360384 is a reply to message #360375] Thu, 20 November 2008 15:03 Go to previous messageGo to next message
prem1
Messages: 5
Registered: November 2008
Junior Member
Jo,

Sorry,that's typo mistake.I have corrected the data.

Holiday table Columns (Lookup table) :
HLDY_DT
HLDY_IND
OBSVD_HLDY_IND

Holiday table will have only holiday dates (10 rows per year).


CALENDER DATE Busines_day_no_of_month
------------------------- ----------------------
11/1/2008 12:00:00.000 AM 0
11/2/2008 12:00:00.000 AM 0
11/3/2008 12:00:00.000 AM 1
11/4/2008 12:00:00.000 AM 2
11/5/2008 12:00:00.000 AM 3
11/6/2008 12:00:00.000 AM 4
11/7/2008 12:00:00.000 AM 5
11/8/2008 12:00:00.000 AM 0
11/9/2008 12:00:00.000 AM 0
11/10/2008 12:00:00.000 AM 6
11/11/2008 12:00:00.000 AM 0
11/12/2008 12:00:00.000 AM 7
11/13/2008 12:00:00.000 AM 8
11/14/2008 12:00:00.000 AM 9
11/15/2008 12:00:00.000 AM 0
11/16/2008 12:00:00.000 AM 0
11/17/2008 12:00:00.000 AM 10
11/18/2008 12:00:00.000 AM 11
11/19/2008 12:00:00.000 AM 12
11/20/2008 12:00:00.000 AM 13
11/21/2008 12:00:00.000 AM 14
11/22/2008 12:00:00.000 AM 0
11/23/2008 12:00:00.000 AM 0
11/24/2008 12:00:00.000 AM 15
11/25/2008 12:00:00.000 AM 16
11/26/2008 12:00:00.000 AM 17
11/27/2008 12:00:00.000 AM 0
11/28/2008 12:00:00.000 AM 0
11/29/2008 12:00:00.000 AM 0
11/30/2008 12:00:00.000 AM 0


Thanks in Advance,
Prem
Re: Need help to find Business day number of the month [message #360582 is a reply to message #360384] Fri, 21 November 2008 08:20 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Prem1,

So what have you tried so far? Did you go through the solutions given by @prtz and the link posted by Michel in this post. Post the queries you have tried so far and explain where exactly are you facing an issue.

Before the next post, Please read OraFAQ Forum Guide on "How to Format Your Post?"

Regards,
Jo
Re: Need help to find Business day number of the month [message #360633 is a reply to message #360582] Fri, 21 November 2008 13:13 Go to previous messageGo to next message
prem1
Messages: 5
Registered: November 2008
Junior Member
Jo,

I am able to get the business day number of month when i include only SAT and SUNDAY in my SQL.If i include holiday dates from Holiday table in the SQL not working.

Getting below output :
Example AUg Ist federal holiday but still showing in the output as working day.

Caldender date Business day no of month
8/1/2008 12:00:00.000 AM 1
8/2/2008 12:00:00.000 AM 0
8/3/2008 12:00:00.000 AM 0
8/4/2008 12:00:00.000 AM 2
8/5/2008 12:00:00.000 AM 3
8/6/2008 12:00:00.000 AM 4
8/7/2008 12:00:00.000 AM 5
8/8/2008 12:00:00.000 AM 6
8/9/2008 12:00:00.000 AM 0
8/10/2008 12:00:00.000 AM 0
8/11/2008 12:00:00.000 AM 7
8/12/2008 12:00:00.000 AM 8
8/13/2008 12:00:00.000 AM 9
8/14/2008 12:00:00.000 AM 10
8/15/2008 12:00:00.000 AM 11
8/16/2008 12:00:00.000 AM 0
8/17/2008 12:00:00.000 AM 0
8/18/2008 12:00:00.000 AM 12
8/19/2008 12:00:00.000 AM 13
8/20/2008 12:00:00.000 AM 14
8/21/2008 12:00:00.000 AM 15
8/22/2008 12:00:00.000 AM 16
8/23/2008 12:00:00.000 AM 0
8/24/2008 12:00:00.000 AM 0
8/25/2008 12:00:00.000 AM 17
8/26/2008 12:00:00.000 AM 18
8/27/2008 12:00:00.000 AM 19
8/28/2008 12:00:00.000 AM 20
8/29/2008 12:00:00.000 AM 21
8/30/2008 12:00:00.000 AM 0
8/31/2008 12:00:00.000 AM 0
Re: Need help to find Business day number of the month [message #360634 is a reply to message #360633] Fri, 21 November 2008 13:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Where is the code?

And once again Mr. stubborn, how about formatting your post to make it more legible? There is a link in the first post of the forum on how to use CODE tags.
Re: Need help to find Business day number of the month [message #360637 is a reply to message #360633] Fri, 21 November 2008 13:37 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Prem1,

I want to ask a couple of things to you:

1. I got the following output when I ran my queries:
SQL> /

CALENDAR_ BUSINESS_DAY_OF_MONTH
--------- ---------------------
01-NOV-08                     0
02-NOV-08                     0
03-NOV-08                     1
04-NOV-08                     2
05-NOV-08                     3
06-NOV-08                     4
07-NOV-08                     5
08-NOV-08                     0
09-NOV-08                     0
10-NOV-08                     6
11-NOV-08                     0

CALENDAR_ BUSINESS_DAY_OF_MONTH
--------- ---------------------
12-NOV-08                     7
13-NOV-08                     8
14-NOV-08                     9
15-NOV-08                     0
16-NOV-08                     0
17-NOV-08                    10
18-NOV-08                    11
19-NOV-08                    12
20-NOV-08                    13
21-NOV-08                    14
22-NOV-08                     0

CALENDAR_ BUSINESS_DAY_OF_MONTH
--------- ---------------------
23-NOV-08                     0
24-NOV-08                    15
25-NOV-08                    16
26-NOV-08                    17
27-NOV-08                     0
28-NOV-08                     0
29-NOV-08                     0
30-NOV-08                     0

30 rows selected.

Did you understand by any chance what code I used or what logic I used by merely looking at the output?

2. Why are you not going through the forum guidelines? Notice the difference in posts between yours and mine. Which one is more understandable or readable?

Apart from joy_division's comment, Only you know what data holds in your holiday table. That's why I asked you to post a test case in the first place.

Regards,
Jo
Re: Need help to find Business day number of the month [message #360703 is a reply to message #360637] Sat, 22 November 2008 15:05 Go to previous messageGo to next message
prem1
Messages: 5
Registered: November 2008
Junior Member
Jo,

Thanks for your data format.I appreciate you.
I am not bale to put data in the order like you.
Here is my test data in holiday table for year 2008..
I need output for business day of the month which should exclude
holidays,SATuRDAYs and SUNDAYs.Holiday dates will come from holiday table.
For each month i need to derive it,so my parameter should be 'YYYYMM'
example '200811' for November month.

HOLIDAY TABLE DATA :
HLDY_DT HLDY_IND
---------- ---------------
01/01/2008 1
01/21/2008 1
02/18/2008 1
05/26/2008 1
07/04/2008 1
09/01/2008 1
10/13/2008 1
11/11/2008 1
11/27/2008 1
11/28/2008 1
12/25/2008 1


My Expected RESULTS :
CALENDAR_DT BUSINESS_DAY_OF_MONTH
----------- ---------------------
01-NOV-08 0
02-NOV-08 0
03-NOV-08 1
04-NOV-08 2
05-NOV-08 3
06-NOV-08 4
07-NOV-08 5
08-NOV-08 0
09-NOV-08 0
10-NOV-08 6
11-NOV-08 0

CALENDAR_DT BUSINESS_DAY_OF_MONTH
----------- ---------------------
12-NOV-08 7
13-NOV-08 8
14-NOV-08 9
15-NOV-08 0
16-NOV-08 0
17-NOV-08 10
18-NOV-08 11
19-NOV-08 12
20-NOV-08 13
21-NOV-08 14
22-NOV-08 0

CALENDAR_DT BUSINESS_DAY_OF_MONTH
----------- ---------------------
23-NOV-08 0
24-NOV-08 15
25-NOV-08 16
26-NOV-08 17
27-NOV-08 0
28-NOV-08 0
29-NOV-08 0
30-NOV-08 0


I am using below SQL :
----------------------
select
TO_DATE(TO_CHAR((TO_DATE(200811,'YYYYMM')-1 + LEVEL), 'YYYYMMDD'), 'YYYYMMDD') CALNDR_DT,
case when TO_DATE(TO_CHAR((TO_DATE(200811,'YYYYMM')-1 + LEVEL), 'YYYYMMDD'),'YYYYMMDD') in
(select F.HLDY_DT from HLDY F where F.HLDY_IND=1
and to_char(F.HLDY_DT,'YYYYMM')='200811')
OR to_char(to_date(200811,'YYYYMM') + level - 1,'Dy') in ('Sat','Sun')
then 0
else
level - sum(case when TO_DATE(TO_CHAR((TO_DATE(200811,'YYYYMM')-1+LEVEL),'YYYYMMDD'),'YYYYMMDD') in
(select F.HLDY_DT from HLDY F where F.HLDY_IND=1
and to_char(F.HLDY_DT,'YYYYMM')='200811')
OR to_char(to_date(200811,'YYYYMM') + level - 1,'Dy') in ('Sat','Sun')
then 1
else 0
end) over (order by to_char(to_date(200811,'YYYYMM') + level - 1,'DDD'))
end as busines_day_no_of_month
from dual
WHERE (TO_DATE(200811,'YYYYMM')-1+LEVEL) <= LAST_DAY(TO_DATE(200811,'YYYYMM'))
CONNECT BY LEVEL <= 31;


Thanks
Prem
Re: Need help to find Business day number of the month [message #360709 is a reply to message #360703] Sat, 22 November 2008 23:49 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thanks for your data format.I appreciate you.

Why don't you do the same thing?

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), align the columns in result and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: How to lock rows fetched by inline view
Next Topic: PLS-00382: expression is of wrong type
Goto Forum:
  


Current Time: Sun Dec 04 10:26:43 CST 2016

Total time taken to generate the page: 0.11708 seconds