T-SQL to Generate A random record and type per person

[rosherguy]

I have a problem that I have built a sort of solution to which seems to work in 99% of cases but I am sure there must be an easier solution to this.

I have a table with a load of records of differing types (they are types of sales with a sales identifier number). What I need to do is to select for each person in a given period a random type of sale with the identifier in week 1 then in week 2 try to select a different type unless all that perosn has sold is the same type as week 1.

My current solution involves row_number and then selections based on where exists and not exists. Does anyone know if there is a much easier solution to do this?

 

[markros]

I think you need to create an inline (or table-valued) function called GetRandomTypes. This function will accept PersonID parameter and it will be something like

select type, row_number() over (order by @PersonID * checksum(NewID()) as Rn from SalesTypes -- I added @PersonID here just so the function will use this parameter (we don't really need it)

Then your main select will be

select P.* cross apply dbo.GetRandomTypes(P.PersonID) F
where P.WeekNumber = F.Rn -- so each week will get a different value

PluralSight Learning Library