How-to randomize in SQL

Few weeks ago I was task to populate dummy data into a table, I have to come up with a price and randomize the ID from 80-90, I have to populate this table with 25000 records!

So for solution, I turned to Rand(), an SQL function that randomizes integer, for quick reference here’s the simple syntax:

Rand [Seed]

a quick example would be :

Then I came up with my own

 

DECLARE @maxValue tinyint = 80
, @minValue tinyint = 90
, @randomId int
, @hid INT
, @amtadd INT;
 
SET @hid=0;
SET @amtadd=20;
WHILE @hid < 2000
BEGIN
 
SET @randomId = Cast(((@maxValue + 1) - @minValue) 
* Rand() + @minValue AS tinyint);
 
INSERT INTO tblDetail (PaymentId,PaymentDate, PaymentAmount,SeqNumber,IsActive,CreateDate,UpdateDate,UpdateUser)
VALUES (@randomId,'2013-10-17',@amtadd,3,1,getdate(),getdate(),'Alf')
 
SET @hid = @hid + 1;
SET @amtadd = Cast(((380 + 1) - 120) 
* Rand() + 120 AS int);
END

 

And there it is!

Grouping Date in SQL

A quick tutorial on how to Group Date in SQL

GROUPING DATE IN SQL
 
SELECT DATEPART(yy, Orders.[date]) AS [y],
       DATEPART(dy, Orders.[date]) AS [d],
       CONVERT(CHAR(12), Orders.[date], 106) AS [date],
       Count(Orders.[ORDER]) AS n
FROM
       [....]
WHERE
       [....]
GROUP BY
       DATEPART(yy, Orders.[date]),
       DATEPART(dy, Orders.[date]),
       CONVERT(CHAR(12), Orders.[date], 106)
ORDER BY
       DATEPART(yy, Orders.[date]),
       DATEPART(dy, Orders.[date])