I have been tasked with generating a report that will randomly pick 10% of a unique ID, unless 10% is less than 20 items in which case the report would pick 20 random ID's. I have been using NewID
to generate the 10%, but that really isn't the best as it gives me variable results (IE: more or less than 10%)
Code also includes my attempts to get a total count of the results:
select UniqueID, TotalCount = Count(*) Over(), SUM(COUNT(UniqueID)) OVER()
AS total_count
from table
where 0.15 >= CAST(CHECKSUM(NEWID(), UniqueID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
group by UniqueID
Aucun commentaire:
Enregistrer un commentaire