I wrote this to calculate the median on a record set. All you need to pass is the table name and column name. If you would like to filter the record set, create a view that displays the correct record.
The limitation of not being able to pass arrays to Stored procedure or functions was the primary reason for creating it in this way.
ALTER Procedure MEDIAN @table varchar(255), @field varchar(255)
AS
BEGIN
DECLARE @medvarcnt int
CREATE TABLE #medianlist2 (rid int IDENTITY(1,1), medianval int)
DECLARE @SQLSTR Nvarchar(800)
SET @SQLSTR =('INSERT #medianlist2 select ' + @field + ' AS medianval from ' + @table + ' order by ' + @field + ' desc')
SET @SQLSTR = CAST (@SQLSTR AS NVARCHAR(800))
EXECUTE sp_executesql @SQLSTR
SET @medvarcnt = (SELECT COUNT(*) FROM #medianlist2)
if @medvarcnt % 2 = 0
BEGIN
SELECT sum(medianval)/2 AS MEDIAN FROM #medianlist2 where rid >=(@medvarcnt/2) and rid <=(@medvarcnt/2)+1
END
ELSE
BEGIN
SELECT medianval AS MEDIAN FROM #medianlist2 where rid =(@medvarcnt/2)+1
END
drop TABLE #medianlist2
END
EXEC MEDIAN @table = 'master.dbo.sysdevices',@field = '[size]' 
Script to Get Total Rows, space used and reserved (KB) of all tables
2006-08-18 (first published: 2006-08-01)
1,084 reads
This script loads from a formatted text file (E.g. tab delimited) with variable number of columns using BULK INSERT. For instance, the file may have any number of columns and next load (seconds later) the same file may have a different number of columns. This script reads the first line (column names) extracting the names […]
2005-03-01 (first published: 2003-06-30)
971 reads
For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.
2003-01-13
18,496 reads
Binary data can be stored as integers in a table. This article explains how to query an integer field to return the bits represented by the integer.
2002-06-28
4,624 reads
This article demonstrates how to store checkbox results as integers in a database...perfect for surveys!
2002-06-27
4,547 reads