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!

setting column value depending on number of month in quarter

Discussion in 'SQL PL/SQL' started by ecivgamer, Aug 1, 2011.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,

    My need is to calculate rows and write result into column:
    c1 if current month is 1st in quarter,
    c2 if current month is 2nd in quarter,
    c3 if current month is 3rd in quarter.

    For example, today is August, it's the second month in quarter, so I should write my value into c2 column.

    How do I perform it?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which release of Oracle are you using? This information make a big difference in how your question is answered.
     
  3. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Oracle 11g.
    My question is resolved, see below. Thanks for attention.

    Code (SQL):
    BEGIN
     
        MERGE INTO marco_tmp_010811b rr
        USING
        (
            SELECT
                111 AS id,
                CASE WHEN months_between(trunc(sysdate+125,'MM'),trunc(sysdate+125,'Q')) = 0 THEN 1 ELSE 0 AS c1,
                CASE WHEN months_between(trunc(sysdate+125,'MM'),trunc(sysdate+125,'Q')) = 1 THEN 1 ELSE 0 AS c2,
                CASE WHEN months_between(trunc(sysdate+125,'MM'),trunc(sysdate+125,'Q')) = 2 THEN 1 ELSE 0 AS c3
            FROM dual
        ) hh
        ON (hh.id = rr.id)
        WHEN matched THEN
        UPDATE SET
            rr.c1 =
            CASE WHEN months_between(trunc(sysdate+125,'MM'),trunc(sysdate+125,'Q')) = 0 THEN rr.c1 + hh.c1 ELSE rr.c1 END,
            rr.c2 =
            CASE WHEN months_between(trunc(sysdate+125,'MM'),trunc(sysdate+125,'Q')) = 1 THEN rr.c2 + hh.c2 ELSE rr.c2 END,
            rr.c3 =
            CASE WHEN months_between(trunc(sysdate+125,'MM'),trunc(sysdate+125,'Q')) = 2 THEN rr.c3 + hh.c3 ELSE rr.c3 END
        WHEN NOT matched THEN
            INSERT (rr.id, rr.c1, rr.c2, rr.c3)
            VALUES (hh.id, hh.c1, hh.c2, hh.c3);   
     
    END;
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Thank you for responding and providing your solution. All benefit from such acts.