Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue in deriving week number

user1014019Jan 4 2016 — edited Jan 5 2016

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  WEEK2NOTE

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,

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 2 2016
Added on Jan 4 2016
18 comments
2,411 views