Format of Serial No in a specific Mannedr

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Format of Serial No in a specific Mannedr

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-05-20 : 07:56:58

In this after ID value had reached 25 it should once again start from 01, 02.

How can I able to do it.

I had give the test table and the insert query.

CREATE TABLE #Test
(
ID VARCHAR(3),
Value VARCHAR(5)
)

INSERT
INTO #Test
(
ID,
Value
)

SELECT LEFT( ('00'+ CAST(ROW_NUMBER() OVER(ORDER BY(SELECT 1)) +
(SELECT (ISNULL(MAX(Id),0))FROM #Test) AS VARCHAR)),
3) AS ID,
--Random string generation
CHAR(94 * RAND() + 33) + CHAR(94 * RAND() + 33) + CHAR(94 * RAND() + 33) + CHAR(94 * RAND() + 33) + CHAR(94 * RAND() + 33) AS Value

SELECT ID,
Value
FROM #Test

Thanks,
Babu kumarasamy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-20 : 08:01:28

[code]((ID - 1) % 25) + 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-20 : 12:42:07

Are you ok with the duplicates you will get from this? Generally serial numbers are unique; a NEWID() value might work well for you.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 12:52:28

not sure why you clear after every 25 values. can you tell the reason for doing this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources