Hi Sir, I am facing an issue in deriving week_number which needs to be derived in the below format The correct format is shown as below
MON-WEEK_NUMBER-YEAR(YY)
MON-WEEK_NUMBER-YEAR
declare
v_cost_pa_period VARCHAR2(10);
v_last_date VARCHAR2(10);
v_sun_date VARCHAR2(10);
v_max_dim_number INTEGER;
v_tues_date VARCHAR2(10);
v_sat_date VARCHAR2(10);
v_last_day NUMBER;
v_sun_number NUMBER;
V_cost_pa_partial VARCHAR2(10);
v_last_date_partial VARCHAR2(10);
v_cost_pa_period1 VARCHAR2(10);
BEGIN
select Initcap(to_char(next_day ( trunc (sysdate, 'iw') , 'Tuesday' ) ,'DD-MON-YY')) into v_tues_date from dual;
dbms_output.put_line('The TUESDAY DATE is '||v_tues_date);
select Initcap(to_char(next_day ( trunc (sysdate-5, 'iw') , 'Saturday' ) ,'DD-MON-YY')) into v_sat_date from dual;
dbms_output.put_line('The SATURDAY DATE is '||v_sat_date);
select to_date(to_char(next_day ( trunc (sysdate-5, 'iw') , 'Sunday' ) ,'DD-MON-YY'),'DD/MM/RRRR') into v_sun_date from dual;
dbms_output.put_line('The SUNDAY DATE is '||v_sun_date);
select last_day(sysdate) into v_last_date from dual;
select TO_NUMBER(Initcap(to_char(next_day ( trunc (sysdate-5, 'iw') , 'Sunday' ) ,'DD'))) into v_sun_number from dual;
dbms_output.put_line('The sunday number is '||v_sun_number);
select case to_char (last_day(sysdate), 'FmDay', 'nls_date_language=english')
when 'Monday' then 1
when 'Tuesday' then 2
when 'Wednesday' then 3
when 'Thursday' then 4
when 'Friday' then 5
when 'Saturday' then 6
when 'sunday' then 7
end d
INTO v_last_day
from dual;
dbms_output.put_line('The v_last_day is '||v_last_day);
select TO_CHAR(SYSDATE,'MON')||'-'||decode(to_char(to_date(sysdate,'MM/DD/YYYY'),'iw')-1,'0','00','1','01','2','02','3','03','4','04','5','05','6','06','7','07','8','08','9','09',to_char(to_date(sysdate,'MM/DD/YYYY'),'iw')-1)||'-'||TO_CHAR(SYSDATE,'YY') into
v_cost_pa_period from dual;
dbms_output.put_line('The cost pa period is '||v_cost_pa_period);
IF v_sun_number < 7 THEN
select NVL(MAX(dim_number),0) into v_max_dim_number from dim_date_20151123;
select to_char (add_months (last_day(sysdate), -1),'MON')||'-'||decode(to_char(to_date(add_months (last_day(sysdate), -1),'MM/DD/YYYY'),'WW'),'0','00','1','01','2','02','3','03','4','04','5','05','6','06','7','07','8','08','9','09',to_char(to_date(add_months (last_day(sysdate), -1),'MM/DD/YYYY'),'WW'))||'-'||TO_CHAR(add_months (last_day(sysdate), -1),'YY')
INTO v_cost_pa_partial from dual;
select TO_CHAR(SYSDATE,'MON')||'-'||decode(to_char(to_date(sysdate,'MM/DD/YYYY'),'WW'),'0','00','1','01','2','02','3','03','4','04','5','05','6','06','7','07','8','08','9','09',to_char(to_date(sysdate,'MM/DD/YYYY'),'WW'))||'-'||TO_CHAR(SYSDATE,'YY') into
v_cost_pa_period1 from dual;
SELECT to_date (add_months (last_day(sysdate), -1),'MM/DD/YYYY') into v_last_date_partial from dual;
insert into dim_date_20151123 values (v_max_dim_number+1,v_cost_pa_partial , v_last_date_partial , NULL, NULL, 'I' );
COMMIT;
select NVL(MAX(dim_number),0) into v_max_dim_number from dim_date_20151123;
insert into dim_date_20151123 values (v_max_dim_number+1,V_cost_pa_partial , v_sun_date , NULL, NULL, NULL );
COMMIT;
select NVL(MAX(dim_number),0) into v_max_dim_number from dim_date_20151123;
insert into dim_date_20151123 values (v_max_dim_number+1,v_cost_pa_period1 , v_sun_date , NULL, NULL, NULL );
COMMIT;
ELSIF V_LAST_DAY = 7 THEN
select NVL(MAX(dim_number),0) into v_max_dim_number from dim_date_20151123;
insert into dim_date_20151123 values (v_max_dim_number+1,v_cost_pa_period , v_last_date , NULL, NULL, 'I' );
commit;
select NVL(MAX(dim_number),0) into v_max_dim_number from dim_date_20151123;
insert into dim_date_20151123 values (v_max_dim_number+1,v_cost_pa_period , v_last_date , NULL, NULL,NULL );
Commit;
ELSE
select NVL(MAX(dim_number),0) into v_max_dim_number from dim_date_20151123;
insert into dim_date_20151123 values (v_max_dim_number+1,v_cost_pa_period , v_sun_date , NULL, NULL, NULL );
Commit;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END;
/
The problem is I am facing is deriving of week_number
From Oracle documentation:
WW: Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
IW : Week of year (1-52 or 1-53) based on the ISO standard.
The issue I am facing is deriving of week_number which is coming as wrong.Now this code runs on every Wednesday and Thursday
This code has given me output as shown below
| DIM_NUMBER | WEEK_NO | WEEKEND_DATE | WEEK1 | WEEK2 | NOTE |
| 1 | DEC-53-15 | 12/31/2015 | | | I |
| 2 | DEC-53-15 | 1/3/2016 | | | |
| 3 | JAN-01-16 | 1/3/2016 | | | |
Actually DEC-53-15 is coming as wrong. It should be coming as DEC-52-15 whereas JAN-01-16 is also coming as wrong.It should be coming as JAN-00-16.
Appreciate your help sir on this.
Regards,