Transact SQL :: How To Assign Random Number Between 0 And 1 To A Record

Nov 17, 2015

I need to assign a random number between 0 and 1 to all eligible cases of cancer. 

I have a file of records like:
patientid
tumid
site

How can I assign a random number to each record?

View 10 Replies


ADVERTISEMENT

How To Assign Random Number To One Column For 500 Records?

Aug 4, 2005

I try to set up a testing sample table which contain one  integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it?
thanks!

View 1 Replies View Related

Loop Through Record Set And Assign Number

Nov 7, 2013

I am new to SQL Server (coming from Oracle background) and have a large table I need to loop thru and assign a number 1 thru 5 on each record.

below is an example of how I would do it in Oracle.

declare
num number := 1;
cursor c_rec is SELECT rowid, t.* FROM temp t order by t.column;
begin
for d_rec in c_rec
loop
update temp set column = to_char(num)

[Code] ....

View 7 Replies View Related

Transact SQL :: Assign Unique Number To Records?

Nov 15, 2015

Lets say I have a table - tblProducts

View 4 Replies View Related

Transact SQL :: Set Unique Random Number

Dec 1, 2015

Lets say we have a table (tblProducts)

ID   Item             RandomNumber
-------------------------------------------
1    JEANS                      1234567
2    SHIRT                    72813550
3    HOOD                             Null
4    TROUSER               72191839
5    BLAZER                              0

I want to perform a query so that SQL should look for RandomNumber Values and set a Unique Random Number Where RandomNumber Value is Null or 0.So I have got a solution as one of the MSDN Member shared the below query

select id,item,RandomNumber=Case when RandomNumber=0 then (select floor(rand()*100000000-1))
when RandomNumber is null then (select floor(rand()*100000000-1))
else RandomNumber end from tblProducts

So, can you all confirm me, that performing this query ensures that if a Value is assigned to one of the Item in RandomNumber Column, that value will not be assignend to any other Item in RandoNumberColumn.

View 15 Replies View Related

Transact SQL :: Retrieve Latest Record Of Serial Number With Multiple Entries

Sep 30, 2015

I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.

I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,

Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.

Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.

SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model

[Code] ....

View 3 Replies View Related

ASSIGN Record Equal

Mar 31, 2006

Good day!

I have the qry, which is suppose to assigned records to active user (almost 15 users) equal, but it doesn’t- sometime it assigned more to some users and less to others. How could I modify my qry to make sure it assigns the records equals to each user? Please, see the qry below. I will appreciate any help

Thk


UPDATE
TBLFRAUDFINDER
SET
DATE_ASSIGNED=GETDATE(),
FRAUDANALYSTASSIGNED=@FRAUDANALYST
WHERE
FRAUDID=@FRAUDID OR
(FRAUDANALYSTASSIGNED IS NULL AND
((INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER) OR
(CUST_NM=@CUST_NM) OR
(ALERT_IDENTIFIER=@ALERT_IDENTIFIER) OR
(ACCT IN (SELECT
ACCT
FROM
TBLFRAUDFINDER
WHERE
INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER OR
CUST_NM=@CUST_NM OR
ALERT_IDENTIFIER=@ALERT_IDENTIFIER))

View 1 Replies View Related

Random Number

Apr 15, 2002

Generates a true random number from xxx to zzz. Uses a table variable so it all depends on the boundaries you specify how long it will take.

create procedure random_number
@lower int = 0,
@upper int = 256,
@number int = null output
as
set nocount on

declare @numbers table (number_id int identity(1,1), value int)

if @lower > @upper
begin
set @number = @lower
set @lower = @upper
set @upper = @number
end

set @number = rand((datepart(mm, getdate()) * 100000) + (datepart(ss, getdate()) * 1000) + datepart(ms, getdate()))

while (select count(*)
from @numbers) < (@upper - @lower + 1)
begin
insert into @numbers (value)
select (@upper - @lower + 1) * rand() + @lower
end

select @number = value
from @numbers
order by newid()
go

View 16 Replies View Related

Random Number

Oct 8, 2005

I need to update an integer column in my table with a random number. Here's what I've done so far (with the table name and column names changed):

UPDATE dbo.MyTable
SET Column1 = RAND() * 1000000

My question is, how come the values in my column all have the same value? I thought the RAND function gives out a random number? How can I update my table to generate random numbers?

Thanks in advance.

View 3 Replies View Related

Get Random Record From SQL Table

Apr 22, 2007

Hi,I am in a situation where our developer is on leave (annual leave for a month), and I have to add a control to my website, which is in aspx apges.To start with i have created a table in my SQL database. this table has records with one-liners from various movies, and the movie title. The tabel have 3 columns, i.e. ID, Liners, MTitle.So i want to get random records on the page when ever its refreshed. I am totally non-coder/programmer guy. I have got a SQL statement from the internet " SELECT TOP 1 * FROM <table name> ORDER By NEWID() "So would anyone please help me out with it, as is it correct, how can i apply in the aspx pages. Thank you. 

View 2 Replies View Related

Random Record Selection

Jul 9, 2007

hello members
i want to no that weathere there is any procedure to select come random records from the database
for eg i want to select 10 students out of 100 randomly
is there any query for this in SQL server 200

View 2 Replies View Related

Flag Random Record

Mar 31, 2006

I am using this select statement to radomly display a recordSelectCommand="SELECT TOP 1 *  FROM [TBL_Example] ORDER BY NEWID()I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement.  I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.Any ideas?

View 1 Replies View Related

Random Record With TOP Parameter

Apr 19, 2004

I'm familiar with querying for a random record, however I can't seem to do it using a parameter driven TOP value.

Here's what I've tried in a stored procedure:
(
@Count int = 1
)
declare @sql varchar(1000)
set @sql = 'SELECT DISTINCT TOP ' + convert(varchar, @Count) + ' * FROM RandomisedView'


The view I am using looks like:

SELECT TOP 100 PERCENT dbo.TableName.* ORDER BY NEWID()

The stored procedure does not seem to cause the View to reorder the records.

I need to be able to select X random records. Any help would be appriciated.

View 1 Replies View Related

Generate A Random Int To Add To A Record

Jul 20, 2005

I need to generate a random whole number between 0 and 999 to add toadd to every record in a table. The number needs to be different foreach record in the table.How would I do this all within TSQL?

View 1 Replies View Related

Update To A Random Number?

Nov 27, 2004

Hi,

How can I update my users table and set the password field to a random number or string.

eg.

update tbl_users set password='a way to generate a random number say between 2000 and 100000 for example'

even better would be a randomly generated alphanumeric string.

All help much appreciated.

Cheers,

RG

View 2 Replies View Related

Random Number Generator

Feb 23, 2005

i have an application that uses a table for login access, for security reason i need to get a random numbers in this table daily. table consist of just two columns, userid and password. This table will be printed out on the web for users to get valid username and pw daily.
I m thinking about a job or dts package that will run once daily with a sql script to generate random number (referable 5 digit-letters and numbers), delete the table and re-create the table with same set column names and insert the random values in username and pw column.
Will appreciate help on this

Thanks

View 1 Replies View Related

How To Generate Random Number For The Value In Th

Aug 2, 2007

How to create random number for the value in other colum. Please help and urgent

I have a table with 4 column ( ID, Ori_Quantity, Rand_Quantity, Location)

If Ori_quantity < 5000 then
Rand_quanty as qty = I want Random number within 100
Else If Ori_quantity = 0 or < 10 then
Rand_quanty as qty = I want Random number within 7
End if

From tblname where Location = 'DAS'

I have around 2500 field. So when I run the query I expect the result should be
like below

Ori_Quantity, Rand_Quantity
------------ -------------
4000 90
2986 57
2500 89

I don't want to pass any parameter and I am new. Please help

View 7 Replies View Related

Random Number-9 Digits Only

Mar 25, 2008

Hi,

I need to generate random numbers that will always contain 9 digits.
How can I do that?
Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 1 Replies View Related

How To Gnerate A Random ID Number

Jun 9, 2007

I just wana make a random id number based on4 digits-for examples??Thanks in AdvanceCh.Yallin

View 21 Replies View Related

Random Number Generator

Sep 28, 2007

In a Name table, I need to generate unique 6 digit random numbers in a field called UniqueID for all records that have the ID field populated.
I will need to run this script periodically. It is critical that any prevoiusly assigned UniqueIDs do not change and only fields that have an empty UniqueID field are updated. I need to preserve the historical mapping of the existing IDs to the ongoing assignment of UniqueIDs.

Regards,

Polar Bear

View 3 Replies View Related

Logs Table - Assign Serial Number To Techs

Jul 30, 2014

I have Logs table and want to assign a serial number to the techs using the following query

Select
Date,
Case_ID,
Site,
Dept,
Tech,
Start_Time,
ROW_NUMBER () OVER (PARTITION BY Date, Site, Dept, Tech ORDER BY Start_Time) as Row_Num
FROM
Logs
Where Date = Getdate()

I get the following results.

Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:352
7/28/14 70004 TartvilleMaintcAmy P.9:103
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:304
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301

Please notice the cases in Maintc department. See how Amy P.'s shift is broken by Rem W. and Mag O. But the Row Number does not recognize this, it still says Amy P's case as 2 and 4 the even though Rem's and Mag's cases were in between.

This is what I really wanted.

Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:351
7/28/14 70004 TartvilleMaintcAmy P.9:102
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:301
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301

I tried many combination of columns for Partition by () and Order by () and the best I can get is the result at the top. How should I achieve it.

View 1 Replies View Related

SELECTing Random Record From TABLE?

Apr 24, 2001

Hello.
I need to select a random record from TABLE. It might look easy with using RAND() function, but the tricky part is that ID's which are the PRIMARY KEY, were assigned as a random number.
So right now ID's in that TABLE look some thing like that: -18745, 45809, 129, -5890023, 487910943, -209, etc...
If any one have any ideas please respond.
Thanks in advance.

View 2 Replies View Related

How Do I Select A Random Record From The Table

Sep 18, 2000

Hello,

I need to write a select statement where I need to select the record randomely. Is there any function to do that? What's the best way to do that? Please help...

Thanks in a million.

Sarika

View 1 Replies View Related

Build Random Phone Number.

Jan 15, 2005

I'm building a test database and I need to randomly create phone numbers in the format xxxxxxxxxx

I have 5000 contacts and need to generate fake phone numbers for them. anyone know how I can write an update query to do this?

ScAndal

View 4 Replies View Related

Generate Random And Unique Number Within Sql ?

Dec 11, 2007

I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?

If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.

thoughts ?

View 5 Replies View Related

Random Number Procedure Problems!

Sep 7, 2006

Hey Guys, I have a quick stored
procedure question for you. I created a procedure that returns a randomly
generated number.

I know there is a built in
Rand() function but I need to be able to
specify the range. Anyways, it€™s returning multiple numbers (1 for each row in
the tempTable)
But I just want one number returned.
I tried using MAX( ) but that€™s not a good way of doing it.


My question is, how do I return just
one record or number?

Thanks
Fellas,

Chris


ALTER PROCEDURE
dbo.spx_GetRandomNum

AS

DECLARE @Start_Num int

DECLARE @End_Num int

DECLARE @ReturnValue
int
DECLARE @RecNum
int
DECLARE @CurrentRec
int
DECLARE @MyCount
int

SET @Start_Num=1

SET
@End_Num=100


CREATE TABLE #tblTemp (CurrentRecNo
int IDENTITY(1,1), RandomNumber int null)

SET @MyCount = ( @End_Num -
@Start_Num ) + 1
SET @CurrentRec = 1


WHILE @CurrentRec <= @MyCount


BEGIN
SET @RecNum
= CAST((RAND() * @MyCount) AS int) + @Start_Num

INSERT INTO
#tblTemp (RandomNumber) VALUES (@RecNum)
SET
@CurrentRec = @CurrentRec + 1

END

SET
@ReturnValue = (SELECT RandomNumber FROM
#tblTemp)

DROP TABLE #tblTemp


RETURN
@ReturnValue
GO

View 4 Replies View Related

Transact SQL :: Repeatedly Assign Set Of Dates To Each ID?

Nov 18, 2015

I have a table with 3 columns , let say (PatientID  int, AppointmentDate date, PatientName varchar(30))

My source data looks in below way..

PatientID        AppointmentDate      PatientName
  1                 01/01/2012          Tom
  2                 01/10/2012          Sam
  3                 02/15/2012          John

I need output in below way..

PatientID        AppointmentDate      PatientName
  1                 01/01/2012          Tom    (actual patient record)
  null              01/10/2012          Tom
  null              02/15/2012          Tom
  null              01/01/2012          Sam
  2                 01/10/2012          Sam     (actual patient record)
  null              02/15/2012          Sam
    null              01/01/2012          John
  null              01/10/2012          John
  3                 02/15/2012          John     (actual patient record)

I need t-sql to get above output. Basically the appointment dates are repeatedly assigned to each patient but only difference is patientid will be not null for actual patient record.
 
Create table sample (PatientID  int null, AppointmentDate date null, PatientName varchar(30) null)

Insert sample  values (1,'01/01/2012' ,'Tom'),
(2,'01/10/2012','Sam'),
(3,'02/15/2012','John')

View 2 Replies View Related

How To Generate Random UNIQUE 13 Digit Number?

Aug 22, 2007

Hi

I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.

How do I generate 13 digit UNIQUE random in sql procedure?

Advance thanks

View 20 Replies View Related

Transact SQL :: Recursive Query To Assign Values

Jul 30, 2015

I'm trying to find it difficult to use recursice CTEs or better solution for a special request below. There are two tables 1) @sizes serves as a lookup or reference for right drive and 2) @test is sample data with different sizes. Assume that I want to evenly distribute the drive letters from table1 to table2 by checking the size available.

E.g.: for the first record in  @test; id = 1 where the size is 50 and it fits in Y: drive -- left over space in Y: = 50

id=2, size 2.5, space available from left over = 50 - 2.5 = 47.5 which again fits into Y:
id = 3, size 51, cannot in fit in Y: drive as there is enough space in Y: to allocate (51 > 47.5)

so pick the next drive check on availability again

DECLARE @sizes TABLE (id TINYINT, size DECIMAL(5,2), drive VARCHAR(3))
INSERT INTO @Sizes
SELECT 1,100.00,'Y:'
UNION ALL
SELECT 2,80.85,'Z:'

[Code] ....

-- My output should look like

col1  ,  val ,   path
A          50      Y:
B           2.5    Y:
C          51     Z:
D          2.6    Y:
E          52      Z:
F           2.7    Y:

View 5 Replies View Related

SQL Server 2014 :: Generate Random Number In Range

Oct 3, 2015

I have a function which generate random number in range :

CREATE FUNCTION Func_Rand
(
@MAX BIGINT ,
@UNIQID UNIQUEIDENTIFIER
)
RETURNS BIGINT
AS
BEGIN
RETURN (ABS(CHECKSUM(@UNIQID)) % @MAX)+1
END
GO

If you run this script you always get result between 1 and 4

SELECT dbo.Func_Rand(4, NEWID())

BUT, in this script sometimes have no result !!!!!!! why??

SELECT 'aa'
WHERE dbo.Func_Rand(4, NEWID()) IN ( 1, 2, 3, 4 )

View 2 Replies View Related

Transact SQL :: Getting Random Rows From Table / Two From Each Group

Sep 2, 2015

I am using Sql Server 2008 R2.I have a existing query that basically says

Select Top 50 Subscriber_ID,  Member_Name, Group_ID
from my_table
order by rand(checksum(newid()))

However the client now wants to have at least two from each group_id. There are 17 different groups.  When I run this as is I get about six of the 17 groups in the results.  How can I change this to get at least two results from each group_id?

View 6 Replies View Related

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table 
select 4

[Code] ....

The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5

View 4 Replies View Related

Transact SQL :: Add Random Dates To Existing Table From Defined Range

Jun 15, 2015

I'm trying to add random dates to date column in existing table, but these need to be week days (Mon-Fri).I'm a beginner in TSQL, worked with MS Access many years - in Access I used to do something a bit different:

DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
Table had ID, I gave a date it would start from (31/08/2015) and then range of ID to apply new date:
UPDATE table1 SET table1 .date = DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
WHERE (((table1 .ID) Between 1 And 5456));

This was applying random dates in range of 31/08/2015 + 5 days, so I could give a starting date of Sunday to get random dates populated over given IDs from Monday to Friday.Now, how can I do it in TSQL?I have a table with ID and dates column. I would like to apply new random dates from some range, but making sure they will be week days.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved