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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics