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 01, 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-Dedicated Group: General Forum Members Last Login: Today @ 8:40 AM Points: 34,535, Visits: 28,698
 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." "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 2013Helpful 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 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
 UDP Broadcaster Group: General Forum Members Last Login: Monday, October 14, 2013 6:43 AM Points: 1,475, Visits: 1,900
 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 Group: General Forum Members Last Login: Today @ 8:40 AM Points: 34,535, Visits: 28,698
 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." "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 2013Helpful 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: Friday, June 14, 2013 12:30 PM Points: 646, Visits: 730
 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-Dedicated Group: General Forum Members Last Login: Today @ 8:40 AM Points: 34,535, Visits: 28,698
 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 2013Helpful Links:How to post code problemsHow to post performance problems
Post #521359
 Posted Saturday, June 21, 2008 8:05 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 8:40 AM Points: 34,535, Visits: 28,698
 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 2013Helpful Links:How to post code problemsHow to post performance problems
Post #521360

 Permissions