auto increment number

Hazel Mahmud

6y1.3k.NET

hello..

how do i make code to auto increment number when date has increase?

eg:

Please help. i am urgently need help

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar6y ago
Accepted answer
The identity key will always give the sequential number in increasing numbers. But, the user has specially mentioned he wants "auto-increment number when date has increased".

How you will manage with the identity key and also he has not mentioned where he will use in either insert or the select statement?

Consider the following example with the data inserted in an unordered manner.
create table MyTable(dtDate Date)

insert into MyTable Values
('2020-7-3'),
('2020-7-10'),
('2020-7-17'),
('2020-5-17'),
('2020-7-24')

Select dtDate
, Row_Number() Over(Order by dtDate) as [Number]
From MyTable
0
Rithik Banerjee6y ago
I agreee with Farhan Ahmed.
Just use IDENTITY(1,1) in number column. It will work well.
+1
Suraj Kumar6y ago
You can try the following SQL Server query using ROW_NUMBER() windowed function.
create table MyTable(dtDate Date)

insert into MyTable Values
('2020-7-3'),
('2020-7-10'),
('2020-7-17'),
('2020-7-24')

Select dtDate
, Row_Number() Over(Order by dtDate) as [Number]
From MyTable
You can check the demo at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d2f0458d423417eccaf5b91b3916ca04
-1
Farhan Ahmed6y ago
CREATE TABLE MyTable
(
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Date date,
Number AS ''+ (CONVERT(VARCHAR(5),Id)) PERSISTED
)

0