Last post Apr 17, 2019 06:21 AM by Ackerly Xu
Apr 05, 2019 02:48 AM|salman behera|LINK
I need financial-wise auto serial no. like:
WHERE ATN--user input text,
1920--current financial year
It should be like 'ATN/01-1920','ATN/02-1920','ATN/03-1920'.....Like this. so:
So in a new financial year, auto serial no should be reset like 'ATN/01-2021'
Please, someone, help me
Apr 05, 2019 12:10 PM|jzero|LINK
Apr 06, 2019 10:21 AM|salman behera|LINK
Apr 06, 2019 02:11 PM|mgebhard|LINK
This is a design question but requirement is not specific enough to provide an accurate approach.
Add a table that has the current fiscal year. You can populate this table manually or using a job that runs nightly. I support an application driven by fiscal year and this is the approach I use.
Add another table that stores the user's input; the "ATN".
Your main table will have 3 columns that make a unique constraint the user input, fiscal year, and an INT. The user input and fiscal year are joined to the two tables above or at least used to populate the main table. When a new record is entered, query
the main table by user input and current fiscal year and return the MAX() +1 INT column. If the result is NULL then return 1. Use this value to populate the INT column in the main table.
Displaying the serial number is simple concatenation.
Apr 15, 2019 09:58 AM|salman behera|LINK
my table column like
i need increment value like SAL/02-18-19,SAL/03-18-19 But when Fn_Year will be 19-20 then Salary_code will be
SAL/01-19-20,SAL/02-19-20...... Like this.
please somebody help..
Apr 17, 2019 06:19 AM|wmec|LINK
Syntax for SQL Server
The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:
CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.
Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).
To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName,LastName)
The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".
Apr 17, 2019 06:21 AM|Ackerly Xu|LINK
Hi salman behera,
Maybe you could try
insert into financial (fn_year,salary_code) values
'20-19',-- input your own finacial year
RIGHT('0'+ -- add a zero
isnull( cast ( - - if isnull then return 01(select Max(number)+1 from -- get max number plus 1 (select Row_number() over (partition by fn_year order by fn_year ) number -- get row_number of specified fn_yearfrom financial
where fn_year = '20-19') -- input your own finacial year temp) as nvarchar ),'01'),2) +'-'+'20-19' -- input your own finacial year
Although this way could get the antoincrement value, this could cause concurrency problem.
If users insert record at the same time into this table , their salary_code may be the same, you had better add a unique constraint to this column.