1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Excel formula to oracle command

Discussion in 'SQL PL/SQL' started by rosy, Aug 6, 2015.

  1. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Dear Everyone please help me convert excel formula to Oracle command as below
    =IF(A2=0,0,IF(WEEKDAY(A2,2)>3,(A2+7-WEEKDAY(A2,1)+IF(WEEKDAY(A2,1)<2,-5,2)),A2+7-WEEKDAY(A2,1)+IF(WEEKDAY(A2,1)<5,-2,5)))
    More detail could you check attached file.

    Thank you so much!
     

    Attached Files:

  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    That logic works out to the following:

    IF cell is zero THEN RETURN zero
    ELSIF cell DOW > Wednesday THEN RETURN (cell date + 7 - DOW) + (IF DOW = Sunday THEN subtract 5 ELSE add 2)
    ELSE RETURN (cell date + 7 - DOW) + (IF DOW NOT Friday or Saturday THEN subtract 2 ELSE add 5)

    The equivalent using CASE (and DECODE) would be something like the following:

    Code (Text):

    CREATE TABLE testtab (
    date1 DATE);

    INSERT INTO testtab VALUES ('15-APR-2013');

    SELECT CASE
      WHEN TO_CHAR(date1, 'D') > 3 THEN
        date1 + 7 - TO_CHAR(date1, 'D') + DECODE(TO_CHAR(date1, 'D'), 7, -5, 2)
      WHEN TO_CHAR(date1, 'D') < 4 THEN
        date1 + 7 - TO_CHAR(date1, 'D') + DECODE(TO_CHAR(date1, 'D'), 5, 5, 6, 5, -2)
      ELSE NULL END AS RESULT
    FROM testtab;

    RESULT  
    ---------
    18-APR-13
     
    rosy likes this.
  3. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Thank you so much for your support. I will trie use it for my purpose. If have problem occur please continue help.
    Thank you agin.
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Rosy. If 'problems' occur, the first thing that needs to happen is that you need to try to resolve them yourself. As I have indicated in an earlier thread -- the people providing assistance here are volunteers who like helping others learn Oracle. No one is here for the express purpose of doing the work for people who are not interested in learning Oracle. The query I gave you works -- in the sense that it executes properly in Oracle and returns data that matches what you requested. If you have problems, it is most likely because your actual requirements are different from what you originally posted.

    In any event, implying that the solution provided to you has problems before you've even tested it is fairly rude.
     
    rosy likes this.
  5. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Thank you for your comment, I will follow it.
    Thank again.
     
  6. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    INSERT INTO testtab VALUES ('15-APR-2013');
    SELECT CASE
    WHEN TO_CHAR(date1 -1 , 'D') > 3 THEN
    date1 + 7 - TO_CHAR(date1, 'D') + DECODE(TO_CHAR(date1, 'D'), 2, -5, 2)
    WHEN TO_CHAR(date1 -1 , 'D') < 4 THEN
    date1 + 7 - TO_CHAR(date1, 'D') + DECODE(TO_CHAR(date1, 'D'), 5, 5, 6, 5, -2)
    ELSE NULL END AS RESULT
    FROM testtab;

    I think it is correct with my situation.
    Thank you so much for your help again.