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.
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.
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)
WHERE milliondollarsalesdate IS NULL
AND sales_person_id IN
( SELECT SALES_PERSON_ID
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.
"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.
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
group by rep_code,revenue