Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to update the close_day (oracle 10.2)
Procedure to update the close_day [message #408059] Sat, 13 June 2009 08:32 Go to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Hi

I need to write a procedure on a table fc_time. Let me describe the table and what exactly I need.
SQL> desc fc_time;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TIME_DT                                            DATE
 DAY_NAME                                           NVARCHAR2(30)
 LAST_DAY_IN_MTH_IND                                NVARCHAR2(1)
 HOLIDAY_IND                                        NVARCHAR2(1)
 WKDAY_IND                                          NVARCHAR2(1)
 CLOSE_DAY                                          NUMBER


A sample data from table fc_time:

SQL> select TIME_DT , DAY_NAME, LAST_DAY_IN_MTH_IND, HOLIDAY_IND, WKDAY_IND, CLOSE_DAY from fc_time;
TIME_DT   DAY_NAME                       L H W  CLOSE_DAY
--------- ------------------------------ - - - ----------
01-JUL-10 Thursday                       N N Y
02-JUL-10 Friday                         N N Y
03-JUL-10 Saturday                       N N N
04-JUL-10 Sunday                         N N N
05-JUL-10 Monday                         N N Y
06-JUL-10 Tuesday                        N Y Y
07-JUL-10 Wednesday                      N N Y
08-JUL-10 Thursday                       N N Y
09-JUL-10 Friday                         N N Y
10-JUL-10 Saturday                       N N N
11-JUL-10 Sunday                         N N N
12-JUL-10 Monday                         N N Y


I need to update the CLOSE_DAY which depends on TIME_DT, HOLIDAY_IND, WKDAY_IND.

Time_DT is nothing but a date. If HOLIDAY_IND is N then that is not a public holiday else that is a public holiday.WKDAY_IND is Y if it is a weekday and N if it is saturday or sunday.

Now CLOSE_DAY should be 1 for the 1st working day of the month. Then it should be 2 for the 2nd and so on.

For example here in the above data for 01-JUL-10 CLOSE_DAY should be 1 for 02-JUL-10 it should be 2. Then for 03-JUL-10 and 04-JUL-10 also it should be 2 as they are sunday. Next working day is 05-JUL-10 so it should be 3.

Like this if there is some public holiday then also the CLOSE_DAY should not increase. For example in the above data for 06-JUL-10 it should be 3 as the HOLIDAY_IND is Y.

Again For last 5 working days of month it should be -5,-4,-3,-2,-1.

If 1st day of the month is a holiday or weekend then that should also be -1.

Thanks in advance for your help.

Cheers
Ridhi
Re: Procedure to update the close_day [message #408060 is a reply to message #408059] Sat, 13 June 2009 08:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Thanks in advance for your help
This wouldn't be help, this woul be doing your job for you. How about you show us what you have attempted so far?
Re: Procedure to update the close_day [message #408065 is a reply to message #408060] Sat, 13 June 2009 09:53 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Hi

I could put the logic to set only the 1st date of month after that I am not getting any clue. If any one can give some clue. I am not asking for a full fledged working procedure. If some one can give a logic how can I frame the procedure I ll try to create the procedure. Till now what i wrote as below:
CREATE OR REPLACE PROCEDURE UPDATE_FC_CLOSE_DAY
 IS

 
BEGIN

if  to_char(fc_time.time_dt,'dd')='01' then 
  if (wkday_ind = 'Y' and holiday_ind = 'N') then
  {
  update fc_time set close_day=1;
  }
  else
  {
  update fc_time set close_day= -1;
  }
  end if;
  
else


end if;


END UPDATE_FC_CLOSE_DAY


Thanks
Ridhi
Re: Procedure to update the close_day [message #408066 is a reply to message #408059] Sat, 13 June 2009 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to post SQL without compile errors.



[Updated on: Sat, 13 June 2009 10:06]

Report message to a moderator

Re: Procedure to update the close_day [message #408076 is a reply to message #408066] Sat, 13 June 2009 10:57 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Sory I did not compile that as the logic itself was not built. The below one is the rectified one. I have added update fc_time set close_day=2; just to avoid sql error.

CREATE OR REPLACE PROCEDURE UPDATE_FC_CLOSE_DAY
 IS

 
BEGIN

if  to_char(fc_time.time_dt,'dd')='01' then 
  if (wkday_ind = 'Y' and holiday_ind = 'N') then
  
  update fc_time set close_day=1;
  
  else
  
  update fc_time set close_day= -1;
  
  end if;
  
else
update fc_time set close_day=2;

end if;


END UPDATE_FC_CLOSE_DAY;
Re: Procedure to update the close_day [message #408077 is a reply to message #408059] Sat, 13 June 2009 11:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Like this if there is some public holiday then also the CLOSE_DAY should not increase. For example in the above data for 06-JUL-10 it should be 3 as the HOLIDAY_IND is Y.

>Again For last 5 working days of month it should be -5,-4,-3,-2,-1.

How does "Oracle" know about holidays & workdays programatically?

You might want to SEARCH this forum for previously posted solutions to this FAQ.
Re: Procedure to update the close_day [message #408079 is a reply to message #408077] Sat, 13 June 2009 11:42 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
>How does "Oracle" know about holidays & workdays programatically?

I have put the logic in my procedure how oracle will know it:
if (wkday_ind = 'Y' and holiday_ind = 'N')
then it will be week day else holiday.

But I am not getting clue how ll I get the field updated accordingly.

>You might want to SEARCH this forum for previously posted solutions to this FAQ.

I am not getting any idea with what key words should I search. If you have any link please provide.
Re: Procedure to update the close_day [message #408081 is a reply to message #408059] Sat, 13 June 2009 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/?SQ=e2d6f2fbc68e3f4af4316f15c89b13ab&t=search&srch=holiday&btn_submit=Search&field=all&am p;forum_limiter=1&search_logic=AND&sort_order=DESC&author=

the UPDATE statements require a WHERE clause to avoid changing every row in table.
Re: Procedure to update the close_day [message #408092 is a reply to message #408059] Sat, 13 June 2009 15:30 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Hi All

I configured my procedure as below:
/* Formatted on 2009/06/14 01:56 (Formatter Plus v4.8.6) */
CREATE OR REPLACE PROCEDURE update_fc_close_day
IS
   CURSOR cur_cl_dy
   IS
      SELECT   time_dt, holiday_ind, wkday_ind
          FROM fc_time
      ORDER BY 1;

   v_time_dt       DATE;
   v_holiday_ind   NVARCHAR2 (1);
   v_wkday_ind     NVARCHAR2 (1);
   counter         NUMBER;
BEGIN
   OPEN cur_cl_dy;

   LOOP
      FETCH cur_cl_dy
       INTO v_time_dt, v_holiday_ind, v_wkday_ind;

      IF (v_wkday_ind = 'Y' AND v_holiday_ind = 'N')
      THEN
         IF TO_CHAR (v_time_dt, 'dd') = '01'
         THEN
            counter := 0;
         END IF;

         counter := counter + 1;
      ELSE
         IF TO_CHAR (v_time_dt, 'dd') = '01'
         THEN
            counter := 0;
         END IF;
      END IF;

      UPDATE fc_time
         SET close_day = counter
       WHERE time_dt = v_time_dt;

      COMMIT;
      EXIT WHEN cur_cl_dy%NOTFOUND;
   END LOOP;

   CLOSE cur_cl_dy;
END update_fc_close_day;


It solved my half of the requirement. That is I got the field close_day is updated to the number of working day. Now I need to update my last 5 working days as -5,-4,-3,-2,-1.

Can any one help please?

Regards
Ridhi
Re: Procedure to update the close_day [message #408093 is a reply to message #408059] Sat, 13 June 2009 15:52 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It is not a good idea to COMMIT within a LOOP.

> Now I need to update my last 5 working days as -5,-4,-3,-2,-1.

query & obtain the maximum business day.
subtract 5 from it.

create new LOOP that gets those 5 business days & UPDATE the values
Re: Procedure to update the close_day [message #408098 is a reply to message #408093] Sun, 14 June 2009 00:08 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Hi all

I could write the procedure to get my result. My procedure is as below:
CREATE OR REPLACE PROCEDURE update_fc_close_day
IS
   CURSOR cur_cl_dy
   IS
      SELECT   time_dt, holiday_ind, wkday_ind
          FROM fc_time
      ORDER BY 1;

   CURSOR cur_business_dy
   IS
      SELECT   SUBSTR (time_key, 1, 6) AS mon, MAX (close_day)
          FROM fc_time
      GROUP BY SUBSTR (time_key, 1, 6)
      ORDER BY 1;

   v_time_dt        DATE;
   v_holiday_ind    NVARCHAR2 (1);
   v_wkday_ind      NVARCHAR2 (1);
   v_month          NVARCHAR2 (6);
   v_business_day   NUMBER;
   counter          NUMBER;
BEGIN
   OPEN cur_cl_dy;

   LOOP
      FETCH cur_cl_dy
       INTO v_time_dt, v_holiday_ind, v_wkday_ind;

      IF (v_wkday_ind = 'Y' AND v_holiday_ind = 'N')
      THEN
         IF TO_CHAR (v_time_dt, 'dd') = '01'
         THEN
            counter := 0;
         END IF;

         counter := counter + 1;
      ELSE
         IF TO_CHAR (v_time_dt, 'dd') = '01'
         THEN
            counter := 0;
         END IF;
      END IF;

      UPDATE fc_time
         SET close_day = counter
       WHERE time_dt = v_time_dt;

      EXIT WHEN cur_cl_dy%NOTFOUND;
   END LOOP;

   COMMIT;

   CLOSE cur_cl_dy;

--------------------------------------------------------------------------
   OPEN cur_business_dy;

   LOOP
      FETCH cur_business_dy
       INTO v_month, v_business_day;

      UPDATE fc_time
         SET close_day = -1
       WHERE SUBSTR (time_key, 1, 6) = v_month AND close_day = v_business_day;

      UPDATE fc_time
         SET close_day = -2
       WHERE SUBSTR (time_key, 1, 6) = v_month
         AND close_day = v_business_day - 1;

      UPDATE fc_time
         SET close_day = -3
       WHERE SUBSTR (time_key, 1, 6) = v_month
         AND close_day = v_business_day - 2;

      UPDATE fc_time
         SET close_day = -4
       WHERE SUBSTR (time_key, 1, 6) = v_month
         AND close_day = v_business_day - 3;

      UPDATE fc_time
         SET close_day = -5
       WHERE SUBSTR (time_key, 1, 6) = v_month
         AND close_day = v_business_day - 4;

      EXIT WHEN cur_business_dy%NOTFOUND;
   END LOOP;

   UPDATE fc_time
      SET close_day = -1
    WHERE close_day = 0;

   COMMIT;

   CLOSE cur_business_dy;
END update_fc_close_day;


Thank you all for your valuable help.

Cheers
Re: Procedure to update the close_day [message #408106 is a reply to message #408098] Sun, 14 June 2009 03:54 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could 5 consecutive updates of the "fc_time" table be rewritten so that you'd use only one UPDATE with CASE?
Previous Topic: Dynamic Record In PL/SQL
Next Topic: insert in seperate columns depending on data
Goto Forum:
  


Current Time: Sun Dec 04 13:01:12 CST 2016

Total time taken to generate the page: 0.10894 seconds