Duplicate issue generating a random number in SQL select statement-CodePudding

I am trying to generate a random number for a couple fields in a SQL statement.

SELECT DISTINCT cp.CMN_PersonsID,substring(replace(convert(varchar(100), NEWID()), '-', ''), 1, 6) AS firstname,
substring(replace(convert(varchar(100), NEWID()), '-', ''), 1, 2) AS middlename,
substring(replace(convert(varchar(100), NEWID()), '-', ''), 1, 12) AS lastname,
CONVERT(numeric(9,0),rand() * 999999999)  AS governmentID,
CONCAT(CAST(RAND() * 10000 AS INT), ' ', substring(replace(convert(varchar(100), NEWID()), '-', ''), 1, 6), ' Road') AS address, 
DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1 DATEDIFF(DAY, '01/01/1945', DATEADD(yyyy, -17,GETDATE()))),'01/01/1945') AS DateofBirth,
LEFT(SUBSTRING (RTRIM(RAND())   SUBSTRING(RTRIM(RAND()),3,11), 3,11),10) AS phonenumber,
pfmap.DBName,
pfmap.stu_award_year_token
INTO #person
FROM dbo.CMN_Persons AS cp
JOIN JHU_PF_StudentMapping pfmap ON pfmap.CMN_PersonsID = cp.CMN_PersonsID
WHERE pfmap.award_year_token IN (2021)
AND cp.CMN_PersonsID IN (
2963806
,2959027
,2972326
,2971359)


CMN_PersonsID   firstname   middlename  lastname    governmentID    address DateofBirth phonenumber DBName  stu_award_year_token
2959027 ECB5A0  31  22890C2BEA8F    792859280   1474 C02C04 Road    1948-03-28 00:00:00.000 9917968589  NR  19147
2963806 72C88B  56  F63336684744    792859280   1474 F7BEA7 Road    1985-03-04 00:00:00.000 9917968589  NR  20566
2971359 12E556  0B  502DF75F7EE8    792859280   1474 A7BFD4 Road    1988-03-30 00:00:00.000 9917968589  PH  35870
2972326 71E01B  C9  105D965474D4    792859280   1474 3F674B Road    1968-07-21 00:00:00.000 9917968589  PH  37181

When the above sql executes, the governmentID, phone number and the generated number for the address field is duplicated for all the records. I have tried several different ways to generate a random number and keep getting the same results.

CodePudding user response：

This seems to work

ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % (999999999 - 100000000 1) 100000000

CodePudding user response：

This query will give you unique random number per row.

select ABS(Checksum(NewID()) % (899999999))   100000000
from table

where

ABS is absolute value

NEWID creates a unique value of type uniqueidentifier

CHECKSUM returns the checksum value computed over a table row

899999999 is the difference between max value 999999999 minus min value 100000000

 Sample output:
 governmentID
 455305581
 420995258
 606125369
 921139874