SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate 5 digit random numbers


Generate 5 digit random numbers

Author
Message
ola_blixten
ola_blixten
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 40
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..Smile
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204797 Visits: 41952
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..Smile


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ola_blixten
ola_blixten
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 40
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..Smile

thanks
sgmunson
sgmunson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16451 Visits: 4626
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)
SmileSmileSmile

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..Smile

thanks


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204797 Visits: 41952
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..Smile


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
MannySingh
MannySingh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2883 Visits: 787
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
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204797 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204797 Visits: 41952
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. Smile

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

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search