Generate 5 digit random numbers

Question

Generate 5 digit random numbers

ola_blixten

SSC Veteran

Points: 272
More actions
June 17, 2008 at 8:40 am

#192695

Hey!
My case:
I want to generate 5 digit random numbers which should start with:
00001
00002
00003.....
and 2 digit for week so from 1-52
and 1 digit for year, from 0-9
And everyting should be in the same row
help me..:)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

ola_blixten (6/17/2008)
Hey!
My case:
I want to generate 5 digit random numbers which should start with:
00001
00002
00003.....
and 2 digit for week so from 1-52
and 1 digit for year, from 0-9
And everyting should be in the same row
help me..:)

No problem... but these aren't "random numbers" and we don't actually know where you want things... what should the entire 8 digit format look like?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ola_blixten SSC Veteran Points: 272 More actions · Answer 2

Okey, it should look like this.

|week|year|serialnum|

01 0 00001

01 0 00002

01 0 00003

01 0 00004

...so 99999 on 1 week, then it turn over to week 02 and continue..:)

thanks

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Okay, but are you really looking for RANDOM numbers, or are you seeking to generate the entire possible sequence of 5 digit numbers, in order, as your example suggests? Or perhaps this is an adapted Auxiliary Table of Numbers concept? How do the week and year concepts play into things? If they have an impact, then perhaps you merely need a quick date generator based on the ATN idea? If you can be more specific, it will be a lot easier to help out...

Steve

(aka smunson)

:):):)

ola_blixten (6/18/2008)
Okey, it should look like this.
|week|year|serialnum|
01 0 00001
01 0 00002
01 0 00003
01 0 00004
...so 99999 on 1 week, then it turn over to week 02 and continue..:)
thanks

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

ola_blixten (6/17/2008)
Hey!
My case:
I want to generate 5 digit random numbers which should start with:
00001
00002
00003.....
and 2 digit for week so from 1-52
and 1 digit for year, from 0-9
And everyting should be in the same row
help me..:)

Sorry... I let this one slip through the cracks...

Do you have a solution or do you still need help on this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MannySingh SSChampion Points: 12465 More actions · Answer 5

you can use this and manipulate this query to code as needed..

DECLARE @GENRANDOM nvarchar(9)

SET @GENRANDOM =LEFT(SUBSTRING (RTRIM(RAND()) + SUBSTRING(RTRIM(RAND()),3,11), 3,11),9)

--SELECT SUBSTRING(@GENRANDOM ,3,1)

SELECT @GENRANDOM as 'RANDOM_NUMBER'

Maninder
www.dbanation.com

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Here's one way to do it in 2005... The neat thing is it doesn't use any RBAR or a sequence table. You could use UPDATE and OUTPUT to return things to the GUI if necessary.

--===== Create a test table to demonstrate with

CREATE TABLE DateTest

(RowNum INT IDENTITY(1,1),

DateCreated DATETIME)

--===== Fill it with a bunch of dated rows

INSERT INTO DateTest

(DateCreated)

SELECT DATEADD(hh,t.n-1,'20000101')

FROM Tally t

--===== Demo the generation of the required numbers

SELECT *,

REPLACE(STR(DATENAME(wk,dt.DateCreated),2) + '-'

+ RIGHT(DATENAME(yy,dt.DateCreated),1),' ','0') + '-'

+ REPLACE(STR(

ROW_NUMBER() OVER

(PARTITION BY STR(DATENAME(wk,dt.DateCreated),2)

+ RIGHT(DATENAME(yy,dt.DateCreated),1)

ORDER BY dt.RowNum),5),' ','0') AS [Wk-Y-NNNNN]

FROM DateTest dt

ORDER BY ROWNUM

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Mani Singh (6/21/2008)
you can use this and manipulate this query to code as needed..
DECLARE @GENRANDOM nvarchar(9)
SET @GENRANDOM =LEFT(SUBSTRING (RTRIM(RAND()) + SUBSTRING(RTRIM(RAND()),3,11), 3,11),9)
--SELECT SUBSTRING(@GENRANDOM ,3,1)
SELECT @GENRANDOM as 'RANDOM_NUMBER'

Yep... you could do that... if the serial numbers were truly random. But I don't believe that's what's actually being asked for even though the word "Random" was used in the request. I believe the OP wants weekly repeating serial numbers. 🙂

Also, how does your code keep from generating the same random number during the same week? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)