Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-28 : 07:37:16
|
| Trevor writes "I am developing a system which requires each user to proof-read approx 10,000 new records per week i.e. records comming in from a data conversion exercise. At first they will be proofing all 100% and then subsequently decreasing the sampling percentage, over a number of weeks, to approx. 10%.I need a single select query which will give me the correct (or very near) number of records i.e. requiring 75% sample will return 75% +-1% records, and the returned records will be evenly, NOT randomly, spread from the first, or near first, record to the last, or near last, record.Additional info: Each record has a unique consequtive reference starting with 1 for the first record thru N for the Nth record.I do not wish to process each record as this will delay the procedure. I would like a single select statment with the necessary formula or formulae based on the total number of records and the given percentage." |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-28 : 08:21:26
|
The PK of the, say, 100th record can be had with-- Set up "100th record" basisDECLARE @intMyRowCount intSELECT @intMyRowCount = 100SET ROWCOUNT = @intMyRowCountSELECT @MyPK = MyPKCOlumnFROM MyTableORDER BY MyPKCOlumnSET ROWCOUNT = 0 -- Release the set limitation-- Output resultSELECT [MyPKCOlumn] = @MyPK you could then use @MyPK in a WHERE clause, or similar, to get that specific record for Proof Reading.Adjust the calculation of @intMyRowCount as appropriate - e.g. divide COUNT(*) into appropriate portions for the sampling you want to use.Kristen |
 |
|
|
|
|
|
|
|
Returning an even spread of records using a give percentage of the total records