DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: PL SQl script to get Date when $1million revenue is reached

  1. #1
    Join Date
    Feb 2013
    Posts
    3

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

    Hi Experts,

    I need help to write a PL SQL script that will show the Date on which a Sales Rep completed a target of $1million sales for a customer account.

    However, for some reason,after touching $1 million, if the sales goes down below $ 1 million and then climbs back again then that Date should not be picked.


    Table structure can be:

    Rep_Code Date Revenue Office_Code


    Can someone please suggest.

    Thanks,
    Prathamesh Acharya

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Many ways to do it... one of them:
    1- Load all rows of a particular Rep_Code into a cursor order by date ASC
    2- Search cursor looking for the first occurrence of the desired threshold
    3- Output such a date.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Thumbs down

    Your requirements are lacking, you need to post the following:

    1) Provide a working test case with create table/insert statements.
    2) Provide proper requirements to generate the output you expect.
    3) A sample output based on the data you will provide.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    If it was me I would have a date field on the salesman/salesperson table that show the
    date that their sales went over $1,000,000. I would then have an update statement that
    would look for salesman/salesperson that have "milliondollarsalesdate" of null and sales
    over one million. You can update the "milliondollarsalesdate" to TRUNC(SYSDATE)
    Something like:

    Code:
    UPDATE salesperson
       SET milliondollarsalesdate=TRUNC(SYSDATE)
     WHERE milliondollarsalesdate IS NULL
       AND sales_person_id IN
         ( SELECT SALES_PERSON_ID
             FROM SALES
            WHERE SALE_DATE > TRUNC(SYSDATE, 'MM')
            GROUP BY SALES_PERSON_ID, customer_id
           HAVING SUM(sale_value) > 1000000 );
    You may want to create a PL/SQL collection of rows to update and update it with a forall,
    that way you can do an inner join rather than a wehre in. The difference is the difference
    between a non-correlated and correlated subquery. And yes you can put this into a PL/SQL
    stored procedure, you need to get the logic first, then figure out how to write it.

  5. #5
    Join Date
    Feb 2013
    Posts
    3
    Hi gandolf989,LKBrwn_DBA,PMPAVB

    Thank you for the idea. Actually, the table will have the record of transaction that happened on a partic-ular 'day' and it will increase/decrease the revenue column amount for each transaction.

    for e.g.

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

    So, I want A>>5-Jan , B>>7-Jan and C>>8-Feb and no C>>12-Feb because it touched $1M again after 8-Feb. I need only the first date that it touched $ 1M.

    So I need a working PL SQL script for achieving this.

    Thanks,
    Prathamesh Acharya

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    "it will increase/decrease the revenue column amount for each transaction."

    That is a terrible idea! Two transactions come in at the same time, one is a new sale and the other is reversing a sale. They each happen simultaneously, therefore either the transaction amount is too high or too low, or you are forced to lock the row when updating the column. You would be far better off having a salesperson_id column on a detail table with everything sold. Thus two products can be sold or returned at the same time without having to do locking and without one change over writing another. Beside when you update the column with each sale how do you go back and report the detail. You are either keeping track of the same data in two different place or creating data that can't be verified.

  7. #7
    Join Date
    Feb 2013
    Posts
    8
    Hi,
    For the query mentioned above i created this sample table

    create table sales
    (
    Rep_Code Varchar2(50),
    Rep_Date Date,
    Revenue Number,
    Office Char(2)
    );

    Then inserted values as
    insert into sales values('A','1-Jan-2013','0.5M','NY');
    insert into sales values('B','1-Jan-2013','0.3M','CA');
    insert into sales values('A','5-Jan-2013','1.0M','NY');
    insert into sales values('B','7-Jan-2013','1.0M','CA');
    insert into sales values('C','6-Feb-2013','0.8M','MT');
    insert into sales values('C','8-Jan-2013','1.0M','MT');
    insert into sales values('C','11-Feb-2013','0.98M','MT');
    insert into sales values('C','12-Feb-2013','1.0M','MT');

    Now i wrote this query to fetch the data this will give the desired value, please check
    select rep_code, max(rep_date),revenue
    from sales
    where revenue='1.0M'
    group by rep_code,revenue

  8. #8
    Join Date
    Feb 2013
    Posts
    8
    Just modifying the query mentioned above, instead of MAX apply MIN to get the first time the person has reached 1$Million amount

    select rep_code, min(rep_date),revenue
    from sales
    where revenue='1.0M'
    group by rep_code,revenue

  9. #9
    Join Date
    Feb 2013
    Posts
    3
    Thank you Paresh.

    I appreciate the quick response of every forum member. This was as quick as we at SAP SCN Crystal Reports group,follow.

    I've conveyed the ideas given by each of you to the client,however, it seems, they are still finalizing on how to push data in this table, whether it should be detailed or aggregate and so on.

    Ofcourse, as per gandolf989's observation, I hope,they would not want to follow the 'terrible idea'

    As per PAVB's suggestion, I think,this would be the most likely approach.

    As per LKBrwn_DBA's suggestion we really need to think on the structure first.

    I will keep this thread updated as and when there is any news from the other end.

    Thank you
    Prathamesh Acharya

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width