update statement to populate a table col with random perc values?

[eo]

I am working on a DW project, and have to massage the data so that down stream development can continue. I have a few rate (percentage) columns (data type money), and at the moment they are all populated with 0.00.
I want to randomly populate them with an update statement with values of between 0.00 and 1.00 (i.e. between 0% and 100%).

I have been trying the following script but it still all shows as 0.00. Any advise?

update dbo.FACT_CO_Advance
	set LoanToValue3 = CONVERT(int, RAND(CHECKSUM(NEWID())) * 1);

EO
Hertfordshire, England

 

[kristof]

Hi,

You can use this:

CREATE VIEW vRandNumber
AS
SELECT ROUND(((100 - 0 -1) * RAND() + 0), 0) as RandNumber

CREATE FUNCTION RandNumber()
RETURNS float
AS
  BEGIN
  RETURN (SELECT RandNumber FROM vRandNumber)
  END
  
select dbo.RandNumber()
from dbo.Product

You need to use a function (dbo.RandNumber()) because if you use it directly, you will always get the same number, within the same statement.

And the function needs to use the view to work.

 

[kristof]

Replace that view above by this, it's shorter:

CREATE VIEW vRandNumber
AS
SELECT ROUND((100 * RAND()) + 1, 0) as RandNumber

 

[gmmastros]

update dbo.FACT_CO_Advance
    set LoanToValue3 = 0.01 * (ABS(CHECKSUM(NEWID())) % 100)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom