Random number generation based on condition
-
Need to generate random number based on ID3 column. Have mentioned each rows logic in remarks. Note: This is a sample data I need to apply on big set of data
Is there any possibility to create a rand number or incrementing number based on condition.
IF condition passes then retain same else generate another one (rand +1)
-
squvi.87 (12/14/2016)
Need to generate random number based on ID3 column. Have mentioned each rows logic in remarks. Note: This is a sample data I need to apply on big set of data
Is there any possibility to create a rand number or incrementing number based on condition.
IF condition passes then retain same else generate another one (rand +1)
Let me see if I can generalize your logic. You would need a 100% consistent rule to decide what value comes next, and it would be easy for some folks to interpret your post as not having that property. I think you mean that ID3 is the only column relevant to what happens to the random number. The first record would get a value, and then everytime the value of ID3 changes from 1 to something else, or from something else to 1, or from something else to yet another different value, there would be a new value for the rand field. One example you haven't covered is what happens if you get two consecutive identical values in ID3 that are not equal to 1. Once you answer that, there should be a way to at least derive a sequence of numbers for the rand field, starting with any given value. Also, what field can be used to determine the order of the incoming records? That's going to be an absolute requirement.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy) -
Indeed there will need to be something (which is definitely not shown) that provides guaranteed order of IDs 1, 2 and 3 as you have it.
Also, you REALLY should make it easy on us to help you by providing a simple CREATE TABLE script and INSERTs to set up your test data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
squvi.87 (12/14/2016)
Need to generate random number based on ID3 column. Have mentioned each rows logic in remarks. Note: This is a sample data I need to apply on big set of data
Is there any possibility to create a rand number or incrementing number based on condition.
IF condition passes then retain same else generate another one (rand +1)
Do you want a random number or an incrementing number?
For example, if ID3 = 1, then you generate a random number, let's say 7
When ID3 = 3, do you want the new number to be 8 or should it be random again, like 42?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams -
This sounds like one of those odd-nut problems that is most easily handled w/ a "quirky" update.
Before using this method in any kind of production code make sure you read Solving the Running Total and Ordinal Rank Problems[/url]
You can start reading at the 1/2 way mark, at the section headed as, Introducing the "Quirky Update".
-- Setup test data --
IF OBJECT_ID('tempdb..#SomeTable', 'U') IS NOT NULL
DROP TABLE #SomeTable;
CREATE TABLE #SomeTable (
ID1 INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ID2 INT NOT NULL,
ID3 INT NOT NULL,
RuleID INT NOT NULL,
RandomNumber INT NULL
);
INSERT #SomeTable (ID2,ID3,RuleID) VALUES
(1,1,1), (1,1,1), (2,1,1), (2, 31,1),
(2,1,2), (2,1,1), (2,29,1), (2,31,1);
IF OBJECT_ID('tempdb..#RandomRule', 'U') IS NOT NULL
DROP TABLE #RandomRule;
CREATE TABLE #RandomRule (
RuleID INT NOT NULL PRIMARY KEY,
RuleDesc VARCHAR(30) NOT NULL
);
INSERT #RandomRule (RuleID,RuleDesc) VALUES
(1,'ID3 = 1'), (2,'ID3 = 1 AND LAG(ID3) = 1');
--=================================================
-- Actual solution using a "quirky" update.
DECLARE
@ID INT,
@RandomNumber INT = ABS(CHECKSUM(NEWID())) % 10000, -- random positive intiger between 1 and 10,000
@LagID3 INT = 1;
UPDATE st SET
@ID = st.ID1,
@RandomNumber = st.RandomNumber = CASE
WHEN st.RuleID = 1 AND st.ID3 = 1 THEN @RandomNumber
WHEN st.RuleID = 2 AND st.ID3 = 1 AND @LagID3 = 1 THEN @RandomNumber
ELSE ABS(CHECKSUM(NEWID())) % 10000 + @RandomNumber -- random positive intiger between 1 and 10,000 AND greater than the revious random number.
END,
@LagID3 = st.ID3
FROM
#SomeTable st WITH (TABLOCKX)
OPTION(MAXDOP 1);
--=================================================
-- Check the results...
SELECT
st.ID1,
st.ID2,
st.ID3,
st.RandomNumber
FROM
#SomeTable st;
Results:
ID1 ID2 ID3 RandomNumber
----------- ----------- ----------- ------------
1 1 1 6321
2 1 1 6321
3 2 1 6321
4 2 31 14207
5 2 1 16016
6 2 1 16016
7 2 29 22121
8 2 31 30616
-
squvi.87 (12/14/2016)
Need to generate random number based on ID3 column. Have mentioned each rows logic in remarks. Note: This is a sample data I need to apply on big set of data
Is there any possibility to create a rand number or incrementing number based on condition.
IF condition passes then retain same else generate another one (rand +1)
As someone else stated, unless you have something in the table to guarantee the correct order, this task is doomed to failure.
--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".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply