CODEWITHSUNDEEP
oracle-databasecounter
user115391
user115391

Reputation: 305

Increment a column value in Oracle

I need to increment a column value based on certain criteria.

I have 2 tables -

Table A has ID, Name, Date, and so on

Id  Name    Date
1   ABC 3/1/2014
2   ABC 4/1/2014
3   ABC 5/1/2014
4   DEF 4/1/2014
5   DEF 5/1/2014
6   HIJ 1/1/2014
7   HIJ 2/1/2014
8   HIJ 3/1/2014
9   HIJ 4/1/2014
10  HIJ 5/1/2014

Table B has Name(foreign Key), Date(will be same as Table A), counter, and so on.

Name    Date    Counter(the value that I need is in this column, it will be zero by default)
ABC 3/1/2014    0
ABC 4/1/2014    1
ABC 5/1/2014    2
DEF 4/1/2014    0
DEF 5/1/2014    1
HIJ 1/1/2014    0
HIJ 2/1/2014    1
HIJ 3/1/2014    2
HIJ 4/1/2014    3
HIJ 5/1/2014    4

I need to increment the counter as above shown in the table. So for each name the counter should reset and start from 0 and the dates for each names will be different, so I need to take that into account(meaning I cannot assume 1/31/2014 to be 0 for all the names. The counter would end at say 5/1/2014. So I need to update the counter till the date has reached 5/1/2014.

What is the best way to accomplish this? I am trying to write a procedure, but not sure how I can update the counter for different names in the cursors. I am open to any suggestion. Thanks.

EDIT::: Updated Code

Update Table B, set counter = 0;

declare

cursor rec is

select a.name, a.postingdate, b.counter
from table A a
inner join table B b
on a.name = b.name
order by a.name, a.date;

begin

    open rec;
    LOOP
        fetch rec into v_name, v_date, v_counter;
        exit...........

**<How can I reset my counter here for different names>**
        if date < to_date ('01-may-2014', 'dd-mon-yy') then v_counter := v_counter +1;  
        end if;

        update table b set counter = v_counter where current of rec;
    END LOOP;
    commit;
END;

Upvotes: 1

Views: 13388

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

The following query should do what you want:

SELECT NAME, TO_CHAR(SOME_DATE, 'MM/DD/YYYY'), ROWCOUNT-1 AS COUNTER
  FROM (SELECT NAME,
               SOME_DATE,
               ROW_NUMBER() OVER (PARTITION BY NAME
                                  ORDER BY ID, NAME, SOME_DATE) ROWCOUNT
          FROM TABLE_A);

SQLFiddle here

Share and enjoy.

Upvotes: 3

Art
Art

Reputation: 5782

Please copy/paste to see results:

SELECT deptno, ename (your_counter-1) new_counter FROM 
 ( SELECT deptno, ename
       , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY deptno, ename) your_counter
FROM scott.emp
);

10  CLARK   0
10  KING    1
10  MILLER  2
20  ADAMS   0
20  FORD    1
20  JONES   2
20  SCOTT   3
....

In your case you partition by Name.

Upvotes: 3

Related Questions