Last post Jan 04, 2018 06:45 AM by fungus.00
Nov 22, 2017 11:39 AM|fungus.00|LINK
I want to start serial number from 01 when date changes.
I wrote a little code for this but I don't know how to do it.
DECLARE @Date varchar(8) = 20171122 -- > Date
DECLARE @GetMax INT
DECLARE @Code VARCHAR(10)
SELECT @GetMax = MAX(CAST(QNum AS INT)) + 1
SELECT @Code = RIGHT(@Date + '0' + CAST(ISNULL(@GetMax,1) AS VARCHAR(10)),10)
INSERT INTO Quot
SELECT * FROM Quot
I'll appreciate if someone solve this issue.
Thanks in advance
Nov 27, 2017 07:38 AM|Deepak Panchal|LINK
you can try to implement the logic to find that date get changed on not.
store the current date in one variable.
then try to fetch the date from last inserted record in the table.
compare that date with the current date.
if date is same then you not need to reset the serial number.
if date not get matched , it means that date is changed now and you can reset the serial number to 01.
so you will find that it will go in the else part only 1 time in a day when date get change.
otherwise it will increment the serial number by 1.
try to implement this logic may solve your issue.
Jan 04, 2018 06:45 AM|fungus.00|LINK
DECLARE @Code INT, @MaxSrNo INT, @Date varchar(10) = CONVERT(VARCHAR(10), GETDATE(), 120)
SELECT @MaxSrNo = COALESCE(MAX(SrNO), 0) + 1
AND QDate = @Date
SET @Code = REPLACE(@Date,'-','')+ CASE WHEN LEN(@MaxSrNo) = 1 THEN '0'+CAST(@MaxSrNo AS VARCHAR(20)) ELSE CAST(@MaxSrNo AS VARCHAR(20)) END
INSERT INTO tbl(QN, QDate, SrNo)
VALUES(@Code, @Date, @MaxSrNo)
SELECT * FROM tbl