Random choosing of 5 records
I have a set of invoices that are associated with different representatives over a year of time. I would like to randomly select 5 invoices for each user for each month for manual review. Can someone explain how (either via TSQL or SSRS) the
best way to create a column that has, for example, an X in it if it should be reviewed or blank if it shouldn't?
Here's a data set example of what I might expect to see. Alice only has two invoices for January so both must be reviewed, but she has 7 in February so only 5 must be randomly chosen.
Thanks!
=========================
Month
Invoice
Rep
Flagged for Review
January
1
Alice
X
January
2
Alice
X
January
3
Bob
January
4
Bob
January
5
Bob
X
January
6
Bob
X
January
7
Bob
X
January
8
Bob
January
9
Bob
X
January
10
Bob
X
January
11
Chris
X
January
12
Chris
X
January
13
Chris
X
January
14
Chris
X
January
15
Chris
January
16
Chris
X
February
20
Alice
February
21
Alice
X
February
33
Alice
X
February
34
Alice
X
February
35
Alice
X
February
36
Alice
X
February
37
Alice
February
19
Bob
February
22
Bob
X
February
23
Bob
X
February
24
Bob
X
February
25
Bob
February
26
Bob
February
27
Bob
X
February
28
Bob
February
29
Bob
X
February
30
Bob
February
17
Chris
X
February
18
Chris
X
February
31
Chris
X
August 8th, 2012 3:47pm
Hi There
Thanks for your posting. Please try this query that might help you(Please change as per your need )
WITH sampleExample AS ( SELECT CostCentre.*, ROW_NUMBER() OVER (PARTITION BY CostCentre.[Unit]
ORDER BY NEWID()) AS rank
FROM DWH_DimCostCentre CostCentre)
SELECT Example.* FROM sampleExample Example WHERE Example.rank <= 5
if you have any query please let me know
Please also see
http://www.sql-server-helper.com/tips/generate-random-records.aspx
Many thanks
Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
August 8th, 2012 5:02pm
This makes logical sense but I don't see how to do something like a top 5 in a group by where I only mark the top 5 by representative by month. Is there maybe a run time way to do it in SSRS with an expression or something?
August 9th, 2012 9:07am