Median?

[gateshosting]

Ok, I have searched everywhere, and came up with a short-term solution. But I still need [oh no... daughter just spilled coffee all over my laptop and desk!!!! ok, it's clean...] help. I developed a statement that gets a median value from an invoicetotals view. Basically it takes the middle 2 values (in case there is an even number of rows) and averages them. The problem is, I want to do this in a customer grouping, to get all values at once. I have done it with everything, except median. It really needs to perform well as this is my biggest issue.

Anyone have any experience with this? I will post code if I need to, but it will probably only be beneficial if someone has had experience with this.

Best regards,

Michael C. Gates

[iburyak]

You can write a function where you pass a group name and return a median.

Function can be used in Select portion of your query so no loops should be made.

If you need help with a function let me know, please.

[gateshosting]

I am new to functions. I just finally learned the real difference between views and stored procedures. Well, sort of, lol...

What are functions used for exactly? I mean, I know what they are used for in VB, .NET, etc., but not SQL.

My existing median SP has to be run individually per customer. So if there are 10 customers, that's 10 scripts sent from my ASP page. It is too much overhead because these big companies are impatient, as am I.

Thanks,

Michael C. Gates

[iburyak]

Try this

[HTML]--1. Create test table
Create table customers(CusID int, Revenue int)
go
--2. Insert test records
insert into customers values (1, 3)
insert into customers values (1, 4)
insert into customers values (1, 5)
insert into customers values (1, 7)
insert into customers values (1, 8)
insert into customers values (1, 9)


insert into customers values (2, 6)
insert into customers values (2, 7)
insert into customers values (2, 8)
insert into customers values (2, 9)
insert into customers values (2, 10)
insert into customers values (2, 11)
insert into customers values (2, 12)

go

--3. Create Function
Create Function GetMedian (@ID int)
returns int
AS
BEGIN
Declare @Median int, @RecordCount int

Declare @TempID table (ID int Identity(1,1), Revenue int)

INSERT INTO @TempID (Revenue)
Select Revenue
FROM customers Where CusID = @ID
ORDER BY Revenue


select @RecordCount = count(*) from @TempID

If @RecordCount = 0 -- no records were found
SELECT @Median = 0
ELSE
BEGIN
If @RecordCount % 2 = 1 -- check if record count is odd
SELECT @Median = Revenue FROM @TempID WHERE Id = (@RecordCount / 2 + 1)
Else
SELECT @Median = (SUM(Revenue) / 2) FROM @TempID WHERE Id in (@RecordCount / 2, @RecordCount / 2 + 1)
END
RETURN @Median
END

-- 4. Get result
select CusID,dbo.GetMe dian(CusID)
from (select distinct CusID from customers) a[/HTML]

[iburyak]

Try this

[PHP]--1. Create test table
Create table customers(CusID int, Revenue int)
go
--2. Insert test records
insert into customers values (1, 3)
insert into customers values (1, 4)
insert into customers values (1, 5)
insert into customers values (1, 7)
insert into customers values (1, 8)
insert into customers values (1, 9)


insert into customers values (2, 6)
insert into customers values (2, 7)
insert into customers values (2, 8)
insert into customers values (2, 9)
insert into customers values (2, 10)
insert into customers values (2, 11)
insert into customers values (2, 12)

go

--3. Create Function
Create Function GetMedian (@ID int)
returns int
AS
BEGIN
Declare @Median int, @RecordCount int

Declare @TempID table (ID int Identity(1,1), Revenue int)

INSERT INTO @TempID (Revenue)
Select Revenue
FROM customers Where CusID = @ID
ORDER BY Revenue


select @RecordCount = count(*) from @TempID

If @RecordCount = 0 -- no records were found
SELECT @Median = 0
ELSE
BEGIN
If @RecordCount % 2 = 1 -- check if record count is odd
SELECT @Median = Revenue FROM @TempID WHERE Id = (@RecordCount / 2 + 1)
Else
SELECT @Median = (SUM(Revenue) / 2) FROM @TempID WHERE Id in (@RecordCount / 2, @RecordCount / 2 + 1)
END
RETURN @Median
END

-- 4. Get result
select CusID,dbo.GetMe dian(CusID)
from (select distinct CusID from customers) a[/PHP]

[iburyak]

Sorry I posted the same twice because I was getting error messages.

Good luck.