SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 rand() for each row of a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Junior Sqler
Starting Member

18 Posts

Posted - 07/04/2013 :  07:15:20  Show Profile  Reply with Quote
Hello!
I have a table with 3 columns:
1.Invoice Number 2. Rating and 3.Checking

Checking is 0 or 1 depending on Rating.

I wrote the following query.


select a.Invoicenumber,a.rating,
(case when a.Rating<=3 then (case when rand() <=0.05 then 1 else 0 end)
when a.Rating>3 and a.Rating<=4 then (case when rand() <= 0.1 then 1 else 0 end)
when a.Rating>4 and a.Rating<=5 then (case when rand()<=0.2 then 1 else 0 end)
else 1 end) as checking
from #temp2

My question is every time i run it rand() takes only one value?
How could i do it so rand() is different for each row of my table?

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/04/2013 :  07:47:21  Show Profile  Reply with Quote
rand gives single value for each execution.

try

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/10/generating-random-numbers-part-ii.aspx


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Junior Sqler
Starting Member

18 Posts

Posted - 07/04/2013 :  08:02:38  Show Profile  Reply with Quote
ook..

I have two more questions:

1. if i want my number to be decimal from 0.00 to 1.00 what should i do

2. I guess in my given query i should replace every rand() with (select top 1 abs(checksum(newid())) as number
from sysobjects s1 cross join sysobjects s2)
right?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/05/2013 :  01:58:20  Show Profile  Reply with Quote
quote:
Originally posted by Junior Sqler

ook..

I have two more questions:

1. if i want my number to be decimal from 0.00 to 1.00 what should i do

2. I guess in my given query i should replace every rand() with (select top 1 abs(checksum(newid())) as number
from sysobjects s1 cross join sysobjects s2)
right?


1.You can generate a 3 didgt random number and divide by 100
2. Yep..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Junior Sqler
Starting Member

18 Posts

Posted - 07/05/2013 :  03:04:18  Show Profile  Reply with Quote
thaank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000