SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Percentage of select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MatiTuk
Starting Member

3 Posts

Posted - 02/06/2013 :  13:26:59  Show Profile  Reply with Quote
I am needing to pull a percentage of data from the database. They are only wanting to see 15% of random records from the query. The original query is using o.id % 7=0, but I am not sure what that does. How can I modify the original query to get 15% of the total rows returned.


Here is the original query that I started with:

declare @MyStart datetime
declare @MyEnd datetime

set @MyStart = '09/01/2012'
set @MyEnd = '09/16/2012'

SELECT distinct 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)
and o.id % 7=0

Thanks for the advice

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 02/06/2013 :  13:50:00  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 02/08/2013 :  07:13:57  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also learn to use unambiguous date formats
http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000