Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Increment Number by Date with Reset

Status
Not open for further replies.

szeiss

Programmer
Apr 5, 2000
137
US
Is there a way to increment a number by date resetting on an id column. For example:

Code:
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
 
DATE IS A RESERVED NAME AND CAN'T BE USED

Code:
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
 
Thank you for your help, greatly appreciated!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top