I have a table in an SQL server 2012. Few days ago I requested help for a picking a random sample of 5% from the table with only one criterion. This forum helped me with the problem and solved it. Thanks for that but this time the criteria are much more complicated. I know there are many wise guys out here and I hope this forum can help me solve this.
- ItemReviewId column is the PK.
- Deleted column will have only "yes" and "No" value.
- Audited column will have only "Yes" and "No" value.
I want to create a stored procedure to do the followings:
- Pick a random sample of 10% of all ItemReviewId for distinct 'UserId' and distinct 'ReviewDate' in a given date range. 10% sample should include- 5% of the total population from Deleted (No) and 5% of the total population from Deleted (Yes). Audited ="Yes" will be excluded from the sample.
For example – A user has 118 records. Out of the 118 records, 17 records have Deleted column value "No" and 101 records have Deleted column value "Yes". I need to pick a random sample of 12 records. Out of those 12 records, 6 should have Deleted column value "No" and 6 should have Deleted column value "Yes".
2. Update Audited to "Check"
In a nutshell pick a sample 5% of the population having Deleted="No" and 5% of the population having Deleted="Yes" and update Audited column value to "Check" of the sample.
How can I achieve this?
Any help would be highly appreciated. Thanks in advance.