Stored procedure for Median
-
I have a table with 5 columns. Based on each priority I have to caluclate the median for Num_of_days_worked took to complete the task. I have to create a new column (Median) and insert that caluclated value in that. Below is the table. I have found from this website Median function
Could somebody please help me in writing Stored procedure.
Type of Request Priority Submit_time Close_time Num days worked KCR Request 1 2006-10-03.1308:10 2006-12-04-10.10.03 2 KCR Request 1 2006-01-03.1308:10 2006-01-08-10.10.03 5 KCR Request 1 2006-01-03.1408:10 2006-01-06-13.22.06 3 KCR Request 1 2006-02-03.1208:10 2006-02-09-10.16.03 6 KCR Request 1 2006-02-08.1208:10 2006-02-14-10.16.03 6 KCR Request 2 2006-01-05.1308:11 2006-01-08-10.10.03 5 KCR Request 2 2006-01-07.1408:13 2006-01-06-13.22.06 3 KCR Request 2 2006-02-03.1208:15 2006-02-09-10.16.03 6 KCR Request 3 2006-01-03.1308:10 2006-01-08-10.10.03 5 KCR Request 3 2006-01-03.1408:10 2006-01-06-13.22.06 3 KCR Request 3 2006-02-03.1208:10 2006-02-09-10.16.03 6 KCR Request 3 2006-02-08.1208:10 2006-02-14-10.16.03 6 MPR Request 1 2006-10-03.1308:10 2006-12-04-10.10.03 2 MPR Request 1 2006-10-03.1308:10 2006-12-04-10.10.03 2 MPR Request 1 2006-10-03.1308:10 2006-12-04-10.10.03 2 MPR Request 1 2006-10-03.1308:10 2006-12-04-10.10.03 2 MPR Request 1 2006-10-03.1308:10 2006-12-04-10.10.03 2 MPR Request 1 2006-10-03.1308:10 2006-12-04-10.10.03 2 .
.
.
drop table MyTable
Create table MyTable
( MyID int
)
go
insert MyTable select 1
insert MyTable select 3
insert MyTable select 5
insert MyTable select 6
insert MyTable select 17
insert MyTable select 17
insert MyTable select 18
insert MyTable select 19
DECLARE @n int DECLARE @sql nvarchar(255) SET @n = (SELECT COUNT(*)
FROM MyTable) IF @n % 2 = 0 IF @n = 2 SET @sql = 'SELECT AVG(CAST(MyID AS Decimal(9,2))) Median FROM MyTable' 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) + ' MyID FROM MyTable ORDER BY MyID DESC) A ORDER BY MyID ASC) B' ELSE SET @sql = 'SELECT TOP 1 MyID Median FROM (SELECT TOP ' + CAST(((@n / 2) + 1) AS varchar) + ' MyID FROM MyTable ORDER BY MyID ASC) A
ORDER BY MyID DESC' EXEC(@SQL)
-
For the sample data provided, is this the wanted output?
Type of Request Priority Median
--------------- -------- ------
KCR Request 1 5.0
KCR Request 2 5.0
KCR Request 3 5.5
MPR Request 1 2.0
Because you mention only Priority, not Type Of Request.
N 56°04'39.16"
E 12°55'05.25" -
Hi Peter,
That is the similar output I am looking for but based on both Type of Request and Priority.
thanks
Suresh Alluri
-
I don't understand why the median, which is something you derive from multiple rows, has a value for every single row...am I am missing something?
-
The median of {1,5,1233} is 5. It is the middle value of a set of numbers, not the average value.
Applicable only if there are odd numbers of values.
The median of {1,5,7,634} is 6, (5+7)/2. The average of the two "middlest" numbers.
Applicable only if there are even numbers of values.
N 56°04'39.16"
E 12°55'05.25" -
I know how to calculate medians and means. I do it pretty much every day, but thanks for the review
I thought each row in the table you posted originally was what you wanted to add a median to. But I think you want another table, that would have a median for each priority. I guess I was confused because you said you wanted to "add a column" which led me to think you wanted to add a column to the same table you pasted into the post.
I think it would be easier to offer help if you posted a sample of what the desired output would be, and whether or not it is a dynamically derived value in a view you are after, or a static one time caculation you want stored historically in a table or whatever.
-
Suresh, use this function to calculate your MEDIAN.
CREATE FUNCTION dbo.fnMedian
(
@TypeOfRequest VARCHAR(20),
@Priority INT,
@InputStyle TINYINT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @Items INT,
@Low INT,
@High INT
DECLARE @Values TABLE (ID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, Value INT)
IF @InputStyle = 0 OR @InputStyle IS NULL -- Ordinary MEDIAN
INSERT @Values
(
Value
)
SELECT Num_of_days_worked
FROM MyTable
WHERE [Type of Request] = @TypeOfRequest
AND Priority = @Priority
ORDER BY Num_of_days_worked
ELSE -- Distinct MEDIAN
INSERT @Values
(
Value
)
SELECT DISTINCT Num_of_days_worked
FROM MyTable
WHERE [Type of Request] = @TypeOfRequest
AND Priority = @Priority
ORDER BY Num_of_days_worked
SELECT @Items = @@ROWCOUNT
IF @Items % 2 = 0 -- Even number of values
SELECT @High = @Items / 2,
@Low = @High - 1
ELSE -- Odd number of values
SELECT @High = @Items / 2,
@Low = @High
RETURN 1.0 * (SELECT AVG(Value) FROM @Values WHERE ID IN (@Low, @High))
END
Call the function with
SELECT DISTINCT [Type of Request],
Priority,
dbo.fnMEDIAN([Type of Request], Priority, 0) Median
FROM MyTable
ORDER BY [Type of Request],
Priority
Or
SELECT DISTINCT [Type of Request],
Priority,
dbo.fnMEDIAN([Type of Request], Priority, 1) Median
FROM MyTable
ORDER BY [Type of Request],
Priority
Depending of which type of median you want. Good luck!
N 56°04'39.16"
E 12°55'05.25" -
Thanks much Peter. It worked.
-
Hi Guys,
Hi Peter,
First , Thanks much for providing solution for the Median.
Little expansion on that query. I need to add a column "number of count" based on priority and Request type the median. Below is the query you provided for median
Could you please help me on that........
thanks
Suersh
This is the output i am looking for
TypeofRequest Priority Median Count
--------------- -------- ------ ------
KCR Request 1 5.0
KCR Request 2 5.0
KCR Request 3 5.5
MPR Request 1 2.0
-
This is the result i am expecting
TypeofRequest Priority Median Count
--------------- -------- ------ ------
KCR Request 1 5.0 5
KCR Request 2 5.0 3
KCR Request 3 5.5 4
MPR Request 1 2.0 6
-
SELECT [Type of Request],
Priority,
MIN(dbo.fnMEDIAN([Type of Request], Priority, 0)) Median,
COUNT(*) Count
FROM MyTable
GROUP BY [Type of Request],
Priority
ORDER BY [Type of Request],
Priority
Or
SELECT [Type of Request],
Priority,
MIN(dbo.fnMEDIAN([Type of Request], Priority, 1)) Median,
COUNT(*) Count
FROM MyTable
GROUP BY [Type of Request],
Priority
ORDER BY [Type of Request],
Priority
N 56°04'39.16"
E 12°55'05.25" -
Thanks Peter.. It worked.
Now I have one more task is that I have 2 stored procedures.
1. is the o/p columns that you created Request-type, Priority, median and ticket count based on Request-type and Priority.
RequstType Priority Median TicketCount
KCR Request 2 6.0 1
KCR Request 4 NULL 3
MPR Request 2 12.0 1
MPR Request 3 60.0 1
TPR Request 2 1.0 2
TPR Request 3 5.0 5
TPR Request 4 NULL 15
2. is the o/p that I created (Based on ur stored procedure) Priority, Median and Ticket Count, median and ticket count based on Priority only.
Priority Median TicketCount
2 4.0 4
3 10.0 6
4 27.0 18
Is there anywhy to get this result in one stored procedure?
If not could you please provide me solution to combine both the results into one like UNIONALL
Could you please suggest me how to do that
-
Yes. Replace the MEDIAN function with this new one. For getting the median over Priority only, call this function with dbo.fnMEDIAN(NULL, Priority, 2) or dbo.fnMEDIAN(NULL, Priority, 3)
CREATE FUNCTION dbo.fnMedian
(
@TypeOfRequest VARCHAR(20),
@Priority INT,
@InputStyle TINYINT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @Items INT,
@Low INT,
@High INT
DECLARE @Values TABLE (ID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, Value INT)
IF @InputStyle = 0 OR @InputStyle IS NULL -- Ordinary MEDIAN over TypeOfRequest and Priority
INSERT @Values
(
Value
)
SELECT Num_of_days_worked
FROM MyTable
WHERE [Type of Request] = @TypeOfRequest
AND Priority = @Priority
ORDER BY Num_of_days_worked
IF @InputStyle = 1 -- Distinct MEDIAN over TypeOfRequest and Priority
INSERT @Values
(
Value
)
SELECT DISTINCT Num_of_days_worked
FROM MyTable
WHERE [Type of Request] = @TypeOfRequest
AND Priority = @Priority
ORDER BY Num_of_days_worked
IF @InputStyle = 2 -- Ordinary MEDIAN over Priority only
INSERT @Values
(
Value
)
SELECT Num_of_days_worked
FROM MyTable
WHERE Priority = @Priority
ORDER BY Num_of_days_worked
IF @InputStyle = 3 -- Distinct MEDIAN over Priority only
INSERT @Values
(
Value
)
SELECT DISTINCT Num_of_days_worked
FROM MyTable
WHERE Priority = @Priority
ORDER BY Num_of_days_worked
SELECT @Items = @@ROWCOUNT
IF @Items % 2 = 0 -- Even number of values
SELECT @High = @Items / 2,
@Low = @High - 1
ELSE -- Odd number of values
SELECT @High = @Items / 2,
@Low = @High
RETURN 1.0 * (SELECT AVG(Value) FROM @Values WHERE ID IN (@Low, @High))
END
N 56°04'39.16"
E 12°55'05.25" -
Call with either.
SELECT Priority,
MIN(dbo.fnMEDIAN(NULL, Priority, 2)) Median,
COUNT(*) Count
FROM MyTable
GROUP BY Priority
ORDER BY Priority
Or
SELECT Priority,
MIN(dbo.fnMEDIAN(NULL, Priority, 3)) Median,
COUNT(*) Count
FROM MyTable
GROUP BY Priority
ORDER BY Priority
N 56°04'39.16"
E 12°55'05.25" -
Hi Peter,
I ran ur query. I got same result as previous first query. I want to combine the both the results into one like below.
RequstType Priority Median TicketCount
KCR Request 2 6.0 1
KCR Request 4 NULL 3
MPR Request 2 12.0 1
MPR Request 3 60.0 1
TPR Request 2 1.0 2
TPR Request 3 5.0 5
TPR Request 4 NULL 15
n/a 2 4.0 4
n/a 3 10.0 6
n/a 4 27.0 18
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply