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 |
|
atulbharadwaj
Starting Member
11 Posts |
 Posted - 2009-12-14 : 23:57:01
|
| hi have create a procedure which is calculating median but its responding very slow, even sometime it takes 10-15 second to respondcode ALTER PROCEDURE [dbo].[Median] @Quarter varchar(10), @Sector varchar(100), @Cap varchar(50), @CurrentYear varchar(10) ASBEGIN SET NOCOUNT ON; declare @temp varchar(200) select @temp = 'SELECT Q.'+ @Quarter +' as '+ @Quarter +' FROM CompanyClassification C inner join EARNINGSCORE Q on C.Bsc_code=Q.Bsc_code where C.sector=' + Char(39) + @Sector + Char(39) + ' AND (C.CAP = ' + Char(39) + @Cap + Char(39) + ') OR (C.CAP = ' + Char(39) + @Cap + Char(39) + ') AND Sector !=' + Char(39) + @Sector + Char(39) + ' and year=' + Char(39) + @CurrentYear + Char(39) + ' and Q.' + Char(39) + @Quarter + Char(39) + ' <> 0 order by Q.' + Char(39) + @Quarter--select @temp--exec(@temp)declare @Median varchar(800) select @Median='SELECT CASE WHEN COUNT(*)%2=1 THEN x.'+ @Quarter +' ELSE (x.'+ @Quarter +' + MIN(CASE WHEN y.'+ @Quarter +' >x.'+ @Quarter +' THEN y.'+ @Quarter +' END))/2.0 END median FROM EARNINGSCORE x, EARNINGSCORE y GROUP BY x.'+ @Quarter +' HAVING SUM(CASE WHEN y.'+ @Quarter +' <= x.'+ @Quarter +' THEN 1 ELSE 0 END)>=(count(*)+1)/2 AND SUM(CASE WHEN y.'+ @Quarter +' >= x.'+ @Quarter +' THEN 1 ELSE 0 END)>=(count(*)/2)+1'--select @Medianexec(@Median)ENDatul |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
Median which can fast response