Increment Number by Date with Reset

[szeiss]

Is there a way to increment a number by date resetting on an id column. For example:

Table1

ContID          Visit     Date
145               1        01/01/2016
145               2        01/25/2016
145               3        02/01/2016
150               1        02/15/2016
150               2        02/28/2016
155               1        01/01/2016
160               1        02/01/2016

I'm inserting data from a temp table into another table, when I insert I want to increment the visit number in date order. Trying not to use a sequence or trigger. Would like to do this using sql and insert statement if possible.

Thanks,
Sherry

 

[Beilstwh]

DATE IS A RESERVED NAME AND CAN'T BE USED

select a.contid,
ROW_NUMBER() OVER (PARTITION BY a.contid ORDER BY my_date) rn,
to_char(my_date,'MM/DD/YYYY') MY_DATE
FROM TABLE1
ORDER BY 1,2,3;

Bill
Lead Application Developer
New York State, USA

 

[szeiss]

Thank you for your help, greatly appreciated!