Randomize values in SQL Server using extra criteria

Question

I need to update the Amount column with a randomized values for every row present in the above table.

However, there are a few rules that I need to meet for this:

• For TransactionType = Deposit, the randomized value must be between $10 and $20.
• For everything else, the randomized value MUST be between between $0.01 and $1 AND LESS OR EQUAL than the original Amount that we are randomizing.

So for example for ID = 7 the randomized value must be between $0.01 and $0.80.

I am struggling to get the results I want with the RAND, CHECKSUM, NEWID functions. Sometimes even getting 0 (zero) as a randomized value for some reason.

This is in SQL Server.

Answer 1

You would need to add another cast to check if the amount is smaller than the 0.99

CREATE TABLE MyTable (
  ID INTEGER,
  UserID INTEGER,
  RecordedTransaction VARCHAR(8),
  RecordedAmount FLOAT
);

INSERT INTO MyTable
  (ID, UserID, RecordedTransaction, RecordedAmount)
VALUES
  ('1', '12345678', 'Purchase', '0.10'),
  ('2', '12345678', 'Deposit', '100.00'),
  ('3', '12345678', 'Purchase', '100.00'),
  ('4', '12345678', 'Deposit', '30.00'),
  ('5', '12345678', 'Purchase', '30.00'),
  ('6', '12345678', 'Deposit', '25.00'),
  ('7', '12345678', 'Purchase', '0.80');

7 rows affected

 UPDATE a 
 SET a.RecordedAmount = case when RecordedTransaction = 'Deposit' THEN 
   FLOOR(RAND(CHECKSUM(NEWID()))* (20- 10 + 1)) + 10 
 ELSE ROUND(
 (CAST(RAND(CHECKSUM(NEWID())) * 
 (CASE WHEN  RecordedAmount < 0.99 THEN RecordedAmount ELSE 0.99 END) AS FLOAT) + 0.01 )
 ,2,0) end
 FROM dbo.MyTable a

7 rows affected

SELECT * fROM MyTable

ID	UserID	RecordedTransaction	RecordedAmount
1	12345678	Purchase	0.07
2	12345678	Deposit	20
3	12345678	Purchase	0.41
4	12345678	Deposit	18
5	12345678	Purchase	0.03
6	12345678	Deposit	17
7	12345678	Purchase	0.1

fiddle

#m with DECIMAL as Datatype it get a bit easier

CREATE TABLE MyTable (
  ID INTEGER,
  UserID INTEGER,
  RecordedTransaction VARCHAR(8),
  RecordedAmount DECIMAL(10,2)
);

INSERT INTO MyTable
  (ID, UserID, RecordedTransaction, RecordedAmount)
VALUES
  ('1', '12345678', 'Purchase', '0.10'),
  ('2', '12345678', 'Deposit', '100.00'),
  ('3', '12345678', 'Purchase', '100.00'),
  ('4', '12345678', 'Deposit', '30.00'),
  ('5', '12345678', 'Purchase', '30.00'),
  ('6', '12345678', 'Deposit', '25.00'),
  ('7', '12345678', 'Purchase', '0.80');

7 rows affected

 UPDATE a 
 SET a.RecordedAmount = 
   case when RecordedTransaction = 'Deposit' THEN FLOOR(RAND(CHECKSUM(NEWID())) * (20 - 10 + 1) + 10) 
 ELSE 
 (CAST(RAND(CHECKSUM(NEWID())) * 
 (CASE WHEN  RecordedAmount < 0.99 THEN RecordedAmount ELSE 0.99 END) AS FLOAT) + 0.01 )
  end
 FROM dbo.MyTable a

7 rows affected

SELECT * fROM MyTable

ID	UserID	RecordedTransaction	RecordedAmount
1	12345678	Purchase	0.02
2	12345678	Deposit	10.00
3	12345678	Purchase	0.41
4	12345678	Deposit	16.00
5	12345678	Purchase	0.98
6	12345678	Deposit	11.00
7	12345678	Purchase	0.56

fiddle