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

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.

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.
:rolleyes:

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.

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

"it will increase/decrease the revenue column amount for each transaction."

That is a terrible idea! :confused: 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.

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

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

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