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  |
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 #343278 is a reply to message #343244] |
Tue, 26 August 2008 17:46   |
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 #360127 is a reply to message #343278] |
Wed, 19 November 2008 12:54   |
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   |
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   |
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   |
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 #360633 is a reply to message #360582] |
Fri, 21 November 2008 13:13   |
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 #360637 is a reply to message #360633] |
Fri, 21 November 2008 13:37   |
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   |
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
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 09:10:46 CST 2025
|