I would like to generate a "random" integer for each row returned from a select statement where the random int only changes once per day (before and after 4am).
Example
declare @Date datetime
set @Date = dateadd(dd, 8, GETDATE())
declare @DateNumber int
set @DateNumber = LEFT(CONVERT(VARCHAR(8), @Date, 112),10)
+ cast(CASE WHEN DATEPART(hh, @Date) > 4 THEN 1 ELSE 0 END as varchar(1))
declare @Customers table (Id int, Customer varchar(150), OrderNo int)
insert into @Customers (Id, Customer) values (1, 'Cust A'), (2, 'Cust B'),
(3, 'Cust C'), (4, 'Cust D'), (5, 'Cust E'), (6, 'Cust F'), (7, 'Cust G'), (8, 'Cust H'), (9, 'Cust I')
-- my 1st attempt which doesnt work
update @Customers set OrderNo = rand(@DateNumber) / rand(Id) * 100
select * from
@Customers order by OrderNo
The order of the results should remain constant until I change the dd value in the set @Date
statement at the top.
Any ideas? Is this possible? (outside of calculating this daily via a SQL job)
updated solution with HABO's recomendation
declare @Date datetime = dateadd(hh, 36, GETDATE())
declare @DateNumber int = LEFT(CONVERT(VARCHAR(8), @Date, 112),10) +
cast(CASE WHEN DATEPART(hh, @Date) > 4 THEN 1 ELSE 0 END as varchar(1))
declare @Customers table (Id int, Customer varchar(150), OrderNo int)
insert into @Customers (Id, Customer) values (1, 'Cust A'), (2, 'Cust B'),
(3, 'Cust C'), (4, 'Cust D'), (5, 'Cust E'), (6, 'Cust F'),
(7, 'Cust G'), (8, 'Cust H'), (9, 'Cust I')
declare @ThrowAway as Float = Rand(@DateNumber)
declare @ID int
set @ID = (select min(ID) from @Customers)
while @ID is not null
begin
update @Customers set OrderNo = Floor(Rand() * 100) + 1 where ID = @ID
set @ID = (select min(ID) from @Customers where ID > @ID)
end
select * from @Customers order by OrderNo
When you provide a seed to RAND( Seed )
it will return the same result. If you use a seed value prior to your UPDATE
query it will initialize the sequence. Thereafter just use RAND()
without an argument. Something like:
declare @ThrowAway as Float = Rand( @DateNumber )
update @Customers
set OrderNo = Floor( Rand() * 100 ) + 1
Do keep in mind that random values include duplicates.