Getting n slip number based on the number of times in a month a slip has been created

Posted on

Question :

Hi Guys so here is my table, I need a slip number for the slipno collom that is based on the number of times the empno has a slipno in a month.

To try and better explain here is my table

----------------------------------
SLIPNO | EMPNO    | DATE         | 
----------------------------------
123    | 0151450  | 07/OCT/17    |
234    | 0151450  | 14/OCT/17    |
345    | 0151460  | 10/OCT/17    |
456    | 0151466  | 05/OCT/17    |
567    | 0151466  | 01/OCT/17    | 
----------------------------------

and need a result like this

-----------------------------------------------
SLIPNO | EMPNO    | DATE         | SLIPNUMBER | 
-----------------------------------------------
123    | 0151450  | 07/OCT/17    | 1          |
234    | 0151450  | 14/OCT/17    | 2          |
345    | 0151460  | 10/OCT/17    | 1          |
456    | 0151466  | 05/OCT/17    | 1          |
567    | 0151466  | 01/OCT/17    | 2          |
-----------------------------------------------    

Answer :

Use the Windowing Function ROW_NUMBER().

SELECT SLIPNO, EMPNO, DT, 
ROW_NUMBER() OVER (PARTITION BY EMPNO ORDER BY DT ASC) AS SLIPNUMBER
FROM SLIPS;

DB Fiddle Test to show an example.

Also note that you should not use DATE as a column name, as it is a reserved word.

Leave a Reply

Your email address will not be published. Required fields are marked *