Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Generate 5 digit random numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, June 17, 2008 8:40 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 1, 2008 2:02 AM Points: 2, Visits: 40
 Hey!My case:I want to generate 5 digit random numbers which should start with:000010000200003.....and 2 digit for week so from 1-52and 1 digit for year, from 0-9And everyting should be in the same rowhelp me..:)
Post #518319
 Posted Tuesday, June 17, 2008 11:06 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:44 AM Points: 42,065, Visits: 39,445
 ola_blixten (6/17/2008)Hey!My case:I want to generate 5 digit random numbers which should start with:000010000200003.....and 2 digit for week so from 1-52and 1 digit for year, from 0-9And everyting should be in the same rowhelp 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." Helpful Links:How to post code problemsHow to post performance problems
Post #518746
 Posted Wednesday, June 18, 2008 1:07 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 1, 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
 SSCrazy Group: General Forum Members Last Login: Today @ 7:43 AM Points: 2,688, Visits: 3,555
 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)Health & Nutrition
Post #519851
 Posted Saturday, June 21, 2008 7:05 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:44 AM Points: 42,065, Visits: 39,445
 ola_blixten (6/17/2008)Hey!My case:I want to generate 5 digit random numbers which should start with:000010000200003.....and 2 digit for week so from 1-52and 1 digit for year, from 0-9And everyting should be in the same rowhelp 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." Helpful Links:How to post code problemsHow to post performance problems
Post #521352
 Posted Saturday, June 21, 2008 7:58 PM
 SSChasing Mays Group: General Forum Members Last Login: Saturday, December 3, 2016 3:34 PM Points: 647, Visits: 757
 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' Maninderwww.dbanation.com
Post #521358
 Posted Saturday, June 21, 2008 8:00 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:44 AM Points: 42,065, Visits: 39,445
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #521359
 Posted Saturday, June 21, 2008 8:05 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:44 AM Points: 42,065, Visits: 39,445
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #521360

 Permissions