A few times I've been asked to give 95th percentile of query results to help sharpen results on reporting where there a some outliers.
-- Select the 95th Percentile of a given query
declare @ct as int
,@ctout as int
,@cut as int
,@cut1 as int
,@cut2 as int
,@q as nvarchar(255)
,@q2 as nvarchar(255)
,@ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@ctout int OUTPUT';
set @q = N'select top 257 * from msdb.dbo.backupfile T ' -- Source Query
set @q2 = N'select @ctout=count(*) from (' + @q + ') x2'
exec sp_executesql @q2,@ParmDefinition, @ctout=@ct OUTPUT;
set @cut = ((@ct-@ct*0.95) / 2 )
set @cut1 = @ct-@cut
set @cut2 = @cut1-@cut
print @ct
print @cut
print @cut1
print @cut2
exec('select top ' + @cut1 + ' * from (' + @q + ') x3 Order by backup_set_id desc')
exec('select top ' + @cut2 + ' * from (select top ' + @cut1 + ' * from (' + @q + ') x3 Order by backup_set_id desc)x4 Order by backup_set_id')
select top 257 * from msdb.dbo.backupfile T Order by backup_set_id desc
1 comment:
Whoa! I’m enjoying the template/theme of this website. It’s simple, yet
effective. A lot of times it’s very hard to get that “perfect balance”
between superb usability and visual appeal. I must say you’ve done a
very good job with this.
oracle apps dba training in Chennai
best java training institute in Chennai
node js developer course in Chennai
Post a Comment