September 7, 2005 at 2:29 pm
Actually, I would like to create SP to calculate median in a table . Therefore, I use table name (probably, column name either) as input parameter for this SP and biuld dynamic string. But the problem is that script inside SP depends on the number of table rows- odd or even. It seems to me that I need to execute 2 dynamic queries inside 1 SP- first, Count(*) .. and second, final result. How to overcome this problem? Thanks.
September 7, 2005 at 2:36 pm
Could you please more clearly define what you are trying to do.
Post a sample table definition, some sample data, and what your expected results are.
It is not likely that you will have to use any dynamic sql, but with the information given it is hard to say.
Posting Etiquette
September 7, 2005 at 2:38 pm
Yuri - to check for even/odd # of rows, you could use this logic..
if (select (count(*) % 2) from myTable) = 0
print 'even'
else
print 'odd'
**ASCII stupid question, get a stupid ANSI !!!**
September 7, 2005 at 2:42 pm
Still requires 2 passes, and there's no way around that.
September 7, 2005 at 2:53 pm
Sorry, if I wasn't clear in my first post. In our forum I found script (from Brian Kelley) how to calculate median for column myId (int) in table myTable (let say, with 1 column) and this script uses dynamic sql. I decided to create stored procedure with tablename and columnname as input parameters. But code flow inside mentioned script depends on number of table rows. That means, I need to execute dynamic sql twice inside SP. Or, may be, there is another approach. Thanks
September 7, 2005 at 2:55 pm
Do the whole thing in dyn sql..
can you post your current code?
September 7, 2005 at 3:09 pm
Sorry, for long post
CREATE PROCEDURE dbo.getmedian
(
@tablename varchar(50)
)
AS
DECLARE @n int, @number varchar(10)
DECLARE @sql nvarchar(255)
SET @sql='SELECT COUNT(*) AS ' + @number + ' FROM ' + @tablename
EXEC(@SQL)
SET @n=(SELECT CAST(@number AS int))
IF @n % 2 = 0 --odd number of numbers
IF @n = 2
SET @sql = 'SELECT AVG(CAST(myId AS Decimal(9,2))) Median FROM ' + @tablename
ELSE
SET @sql = 'SELECT AVG(CAST(myId AS Decimal(9,2))) Median FROM
(SELECT TOP 2 myId FROM ' +
'(SELECT TOP ' + CAST(((@n / 2) + 1) AS varchar) + ' sales FROM ' + @tablename + 'ORDER BY sales DESC) A
ORDER BY myId ASC) B'
ELSE --even number of numbers
SET @sql = 'SELECT TOP 1 myId Median FROM
(SELECT TOP ' + CAST(((@n / 2) + 1) AS varchar) + ' myId FROM ' + @tablename + ' ORDER BY myId ASC) A
ORDER BY myId DESC'
EXEC(@SQL)
GO
Thanks
September 7, 2005 at 3:23 pm
Another nice solution (with a single query) can be found here:
http://www.sqlserverprofessionalnewsletter.com/Media/MediaManager/SQL_Sample.pdf#search='sql%20calculate%20median'
September 7, 2005 at 3:26 pm
Thanks, David. I'll check in a while
September 7, 2005 at 7:09 pm
Select rowcnt from sysindexes
where id = object_id (@TableName) and indid in (0,1)
0 exists for all tables without PK, 1 is PK index. Any particular table has only one of those indexes.
_____________
Code for TallyGenerator
September 7, 2005 at 8:20 pm
Sergiy - not sure if you've accidentally posted the response to some other post over here ?!?!
**ASCII stupid question, get a stupid ANSI !!!**
September 7, 2005 at 9:53 pm
No, that was a query to get COUNT without actually performing count.
Server does COUNT everytime table is updated and stores value in sysindexes. So why do it again and again?
And this allows to replace 1st dynamic statement with static one.
_____________
Code for TallyGenerator
September 7, 2005 at 10:06 pm
SysIndexes are not reliable, those stats are not always up to date. Count(*) is the only safe solution for this problem if accuracy is required.
September 8, 2005 at 8:20 am
Agree with RGR'us- already had issues using system table trying to count row number. Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply