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!

PL SQl script to get Date when $1million revenue is reached for first time

Discussion in 'SQL PL/SQL' started by OracleSAP BI-BW, Feb 13, 2013.

  1. Hi Experts,

    I need help in writing a PL SQL script that will pick up the Date on which a Sales Rep's revenue touched $1M for a particular 'Customer Account'.

    for e.g.

    Rep_Code Date Revenue Office Customer Account
    A 1-Jan-2013 0.5M NY X
    B 1-Jan-2013 0.3M CA Y
    A 5-Jan-2013 1.0M NY X
    B 7-Jan-2013 1.0M CA Y
    C 6-Feb-2013 0.8M MT Z
    C 8-Feb-2013 1.0M MT Z
    C 11-Feb-2013 0.98M MT Z
    C 12-Feb-2013 1.0M MT Z

    So, I want A>>5-Jan , B>>7-Jan and C>>8-Feb.. C>>12-Feb is not required because it touched $1M again after 8-Feb.

    Please suggest.

    Thanks,
    Prathamesh Acharya
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No need for PL/SQL routine, just group by will do the trick.

    Code (SQL):
    SQL> SELECT * FROM REVENUE;

    REP_CODE   T_DATE       REVENUE OFFIC
    ---------- --------- ---------- -----
    C          12-FEB-13    1000000 MT
    C          11-FEB-13     980000 MT
    C          08-FEB-13    1000000 MT
    C          06-FEB-13     800000 MT
    B          07-JAN-13    1000000 CA
    A          05-JAN-13    1000000 NY
    B          01-JAN-13     300000 CA
    A          01-JAN-13     500000 NY

    8 ROWS selected.

    SQL> SELECT REP_CODE ,MIN(T_DATE) CROSS_DATE
      2  FROM REVENUE
      3  WHERE REVENUE >= 1000000
      4  GROUP BY REP_CODE;

    REP_CODE   CROSS_DAT
    ---------- ---------
    C          08-FEB-13
    B          07-JAN-13
    A          05-JAN-13

    SQL>