Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate 5 digit random numbers Expand / Collapse
Author
Message
Posted Tuesday, June 17, 2008 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 01, 2008 2:02 AM
Points: 2, 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..:)
Post #518319
Posted Tuesday, June 17, 2008 11:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:45 PM
Points: 36,013, Visits: 30,300
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #518746
Posted Wednesday, June 18, 2008 1:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 01, 2008 2:02 AM
Points: 2, 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..:)

thanks
Post #518787
Posted Thursday, June 19, 2008 7:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 7:20 AM
Points: 1,528, Visits: 1,971
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)

Internet ATM Machine
Post #519851
Posted Saturday, June 21, 2008 7:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:45 PM
Points: 36,013, Visits: 30,300
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #521352
Posted Saturday, June 21, 2008 7:58 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 02, 2014 10:30 AM
Points: 646, Visits: 731
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
Post #521358
Posted Saturday, June 21, 2008 8:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:45 PM
Points: 36,013, Visits: 30,300
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #521359
Posted Saturday, June 21, 2008 8:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:45 PM
Points: 36,013, Visits: 30,300
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #521360
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse