The current query returns one out of every 7 records - so 14.29 percent.
You can get rid of that "and o.id % 7=0" and use a "TOP 15 PERCENT" clause like this:declare @MyStart datetime
declare @MyEnd datetime
set @MyStart = '09/01/2012'
set @MyEnd = '09/16/2012'
SELECT DISTINCT TOP 15 PERCENT o.order_id,o.first_name,o.last_name ,CAST(maxdate AS VARCHAR(23)) AS completeddate,i.phys_name,i.ins_code,
FROM order o WITH (nolock)
INNER JOIN inservice i WITH (nolock) ON a.ins_id = i.ins_id,
(SELECT MAX(completed_date) maxdate,app_id
FROM wwhs_svc WITH (nolock)
WHERE completed_date between @MyStart AND @MyEnd
ROUP BY app_id) s
WHERE a.order_id = s.order_id
and i.ins_code in ('1245','1875','1920')
ORDER BY NEWID();The order by clause is required only if you want random 15%.
You can change last line to "o.id%100 < 15", but unless you have a very large number of records, or unless the number of records happen to be an exact multiple of 100, that will not be accurate. If you have only a few records (like 10 records), it will be completely wrong.
Percentage of select
Posted - 02/06/2013 : 13:26:59
