I am creating an application that selects ten random customers from a table and writes them to a temp table and displays them for the user who then will click a button to write them to a final table (log). When they are written to that table I want it to find the max value in that table in the format xx-xxxxxxx where xx is the year and xxxxxxx is a number that will increment by 1 for each row written to the table. So if the current max log_no in that table is 16-0000010 then next one written will be 16-0000011, 16-0000012, and so on to 16-0000020. I have the following code that converts the data to an integer and increments it and then converts it back but I am not sure of the best way to do this so it increments for each row. I can't add the row number because when the year changes I want the number to reset to 17-0000001. Any suggestion is appreciated.
> IF Object_ID('tempdb..##Random') is not null > DROP TABLE dbo.##Random > CREATE TABLE ##Random > ( > my columns > ) > DECLARE @maxlog varchar(100) > DECLARE @SeqNo varchar(20) > DECLARE @Year varchar(2) > SET @maxlog = (select right('00000000000000'+ convert(varchar(7),right(max(right(logSeq_num, 7)),2) + 1), 7) FROM log) > set @Year = (select right(datepart(yy, getdate()),2)) > set @seqNo = @Year+'-'+@maxlog > INSERT INTO ##Random > SELECT TOP 10 columns > FROM tables > WHERE > ORDER BY NEWID() > END