obtener moda mediana sql sql-server aggregate-functions median

moda - sql mean function



Función para calcular la mediana en SQL Server (28)

Acabo de encontrar esta página mientras buscaba una solución basada en conjuntos para la mediana. Después de ver algunas de las soluciones aquí, se me ocurrió lo siguiente. La esperanza es ayuda / funciona

DECLARE @test TABLE( i int identity(1,1), id int, score float ) INSERT INTO @test (id,score) VALUES (1,10) INSERT INTO @test (id,score) VALUES (1,11) INSERT INTO @test (id,score) VALUES (1,15) INSERT INTO @test (id,score) VALUES (1,19) INSERT INTO @test (id,score) VALUES (1,20) INSERT INTO @test (id,score) VALUES (2,20) INSERT INTO @test (id,score) VALUES (2,21) INSERT INTO @test (id,score) VALUES (2,25) INSERT INTO @test (id,score) VALUES (2,29) INSERT INTO @test (id,score) VALUES (2,30) INSERT INTO @test (id,score) VALUES (3,20) INSERT INTO @test (id,score) VALUES (3,21) INSERT INTO @test (id,score) VALUES (3,25) INSERT INTO @test (id,score) VALUES (3,29) DECLARE @counts TABLE( id int, cnt int ) INSERT INTO @counts ( id, cnt ) SELECT id, COUNT(*) FROM @test GROUP BY id SELECT drv.id, drv.start, AVG(t.score) FROM ( SELECT MIN(t.i)-1 AS start, t.id FROM @test t GROUP BY t.id ) drv INNER JOIN @test t ON drv.id = t.id INNER JOIN @counts c ON t.id = c.id WHERE t.i = ((c.cnt+1)/2)+drv.start OR ( t.i = (((c.cnt+1)%2) * ((c.cnt+2)/2))+drv.start AND ((c.cnt+1)%2) * ((c.cnt+2)/2) <> 0 ) GROUP BY drv.id, drv.start

Según MSDN , Median no está disponible como una función agregada en Transact-SQL. Sin embargo, me gustaría saber si es posible crear esta funcionalidad (utilizando la función Create Aggregate , la función definida por el usuario o algún otro método).

¿Cuál sería la mejor manera (si es posible) de hacer esto? ¿Permitir el cálculo de un valor mediano (suponiendo un tipo de datos numéricos) en una consulta global?


Aun mejor:

SELECT @Median = AVG(1.0 * val) FROM ( SELECT o.val, rn = ROW_NUMBER() OVER (ORDER BY o.val), c.c FROM dbo.EvenRows AS o CROSS JOIN (SELECT c = COUNT(*) FROM dbo.EvenRows) AS c ) AS x WHERE rn IN ((c + 1)/2, (c + 2)/2);

¡Del maestro mismo, sqlperformance.com/2012/08/t-sql-queries/median !


Aunque la solución de Justin Grant parece sólida, descubrí que cuando tienes una cantidad de valores duplicados dentro de una clave de partición determinada, los números de fila para los valores duplicados de ASC terminan fuera de secuencia, por lo que no se alinean correctamente.

Aquí hay un fragmento de mi resultado:

KEY VALUE ROWA ROWD 13 2 22 182 13 1 6 183 13 1 7 184 13 1 8 185 13 1 9 186 13 1 10 187 13 1 11 188 13 1 12 189 13 0 1 190 13 0 2 191 13 0 3 192 13 0 4 193 13 0 5 194

Usé el código de Justin como base para esta solución. Aunque no es tan eficiente dado el uso de tablas derivadas múltiples, resuelve el problema de ordenamiento de filas que encontré. Cualquier mejora sería bienvenida ya que no soy tan experimentado en T-SQL.

SELECT PKEY, cast(AVG(VALUE)as decimal(5,2)) as MEDIANVALUE FROM ( SELECT PKEY,VALUE,ROWA,ROWD, ''FLAG'' = (CASE WHEN ROWA IN (ROWD,ROWD-1,ROWD+1) THEN 1 ELSE 0 END) FROM ( SELECT PKEY, cast(VALUE as decimal(5,2)) as VALUE, ROWA, ROW_NUMBER() OVER (PARTITION BY PKEY ORDER BY ROWA DESC) as ROWD FROM ( SELECT PKEY, VALUE, ROW_NUMBER() OVER (PARTITION BY PKEY ORDER BY VALUE ASC,PKEY ASC ) as ROWA FROM [MTEST] )T1 )T2 )T3 WHERE FLAG = ''1'' GROUP BY PKEY ORDER BY PKEY


Con frecuencia, es posible que necesitemos calcular Median no solo para toda la tabla, sino también para agregados con respecto a alguna ID. En otras palabras, calcule la mediana para cada ID en nuestra tabla, donde cada ID tiene muchos registros. (basado en la solución editada por @gdoron: buen rendimiento y funciona en muchos SQL)

SELECT our_id, AVG(1.0 * our_val) as Median FROM ( SELECT our_id, our_val, COUNT(*) OVER (PARTITION BY our_id) AS cnt, ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rnk FROM our_table ) AS x WHERE rnk IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;

Espero eso ayude.


El ejemplo anterior de Justin es muy bueno. Pero esa necesidad de la clave primaria debe establecerse muy claramente. He visto ese código en la naturaleza sin la clave y los resultados son malos.

La queja que recibo sobre el Percentile_Cont es que no le dará un valor real del conjunto de datos. Para llegar a una "mediana" que es un valor real del conjunto de datos, use Percentile_Disc.

SELECT SalesOrderID, OrderQty, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY OrderQty) OVER (PARTITION BY SalesOrderID) AS MedianCont FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY SalesOrderID DESC



En un UDF, escribe:

Select Top 1 medianSortColumn from Table T Where (Select Count(*) from Table Where MedianSortColumn < (Select Count(*) From Table) / 2) Order By medianSortColumn


Esta es la solución más óptima para encontrar medianas que pueda imaginar. Los nombres en el ejemplo se basan en el ejemplo de Justin. Asegúrese de que exista un índice para la tabla Sales.SalesOrderHeader con las columnas de índice CustomerId y TotalDue en ese orden.

SELECT sohCount.CustomerId, AVG(sohMid.TotalDue) as TotalDueMedian FROM (SELECT soh.CustomerId, COUNT(*) as NumberOfRows FROM Sales.SalesOrderHeader soh GROUP BY soh.CustomerId) As sohCount CROSS APPLY (Select soh.TotalDue FROM Sales.SalesOrderHeader soh WHERE soh.CustomerId = sohCount.CustomerId ORDER BY soh.TotalDue OFFSET sohCount.NumberOfRows / 2 - ((sohCount.NumberOfRows + 1) % 2) ROWS FETCH NEXT 1 + ((sohCount.NumberOfRows + 1) % 2) ROWS ONLY ) As sohMid GROUP BY sohCount.CustomerId

ACTUALIZAR

Estaba un poco inseguro sobre qué método tiene el mejor rendimiento, así que hice una comparación entre mi método Justin Grants y Jeff Atwoods ejecutando consultas basadas en los tres métodos en un lote y el costo por lotes de cada consulta fueron:

Sin índice:

  • Mina 30%
  • Justin Grants 13%
  • Jeff Atwoods 58%

Y con índice

  • Mina 3%.
  • Justin Grants 10%
  • Jeff Atwoods 87%

Traté de ver qué tan bien escalan las consultas si tienes índice al crear más datos de alrededor de 14 000 filas por un factor de 2 hasta 512, lo que significa al final alrededor de 7,2 millones de filas. Tenga en cuenta que me aseguré de que el campo CustomeId fuera único para cada vez que hice una única copia, por lo que la proporción de filas en comparación con la instancia única de CustomerId se mantuvo constante. Mientras hacía esto, ejecuté ejecuciones en las que reconstruí el índice después, y noté que los resultados se estabilizaron en torno a un factor de 128 con los datos que tenía para estos valores:

  • Mina 3%.
  • Justin Grants 5%
  • Jeff Atwoods 92%

Me pregunté cómo el rendimiento podría haberse visto afectado por la cantidad de filas escaladas, pero manteniendo constante CustomerId único, así que configuré una nueva prueba en la que solo hice esto. Ahora, en lugar de estabilizar, la relación del costo del lote se mantuvo divergente, también en lugar de alrededor de 20 filas por ID de cliente por promedio, tuve al final alrededor de 10000 filas por cada identificador único. Los números donde:

  • Mina 4%
  • Justins 60%
  • Jeffs 35%

Me aseguré de implementar correctamente cada método al comparar los resultados. Mi conclusión es que el método que utilicé generalmente es más rápido, siempre y cuando el índice exista. También notó que este método es lo que se recomienda para este problema en particular en este artículo https://www.microsoftpressstore.com/articles/article.aspx?p=2314819&seqNum=5

Una forma de mejorar aún más el rendimiento de las llamadas posteriores a esta consulta es mantener la información de recuento en una tabla auxiliar. Incluso podría mantenerlo activando un desencadenador que actualice y contenga información sobre el recuento de las filas de SalesOrderHeader dependientes de CustomerId; por supuesto, también puede almacenar la mediana fácilmente.


Esta es una respuesta tan simple como podría haberlo hecho. Funcionó bien con mis datos. Si desea excluir ciertos valores, simplemente agregue una cláusula where a la selección interna.

SELECT TOP 1 ValueField AS MedianValue FROM (SELECT TOP(SELECT COUNT(1)/2 FROM tTABLE) ValueField FROM tTABLE ORDER BY ValueField) A ORDER BY ValueField DESC


Esto funciona con SQL 2000:

DECLARE @testTable TABLE ( VALUE INT ) --INSERT INTO @testTable -- Even Test --SELECT 3 UNION ALL --SELECT 5 UNION ALL --SELECT 7 UNION ALL --SELECT 12 UNION ALL --SELECT 13 UNION ALL --SELECT 14 UNION ALL --SELECT 21 UNION ALL --SELECT 23 UNION ALL --SELECT 23 UNION ALL --SELECT 23 UNION ALL --SELECT 23 UNION ALL --SELECT 29 UNION ALL --SELECT 40 UNION ALL --SELECT 56 -- --INSERT INTO @testTable -- Odd Test --SELECT 3 UNION ALL --SELECT 5 UNION ALL --SELECT 7 UNION ALL --SELECT 12 UNION ALL --SELECT 13 UNION ALL --SELECT 14 UNION ALL --SELECT 21 UNION ALL --SELECT 23 UNION ALL --SELECT 23 UNION ALL --SELECT 23 UNION ALL --SELECT 23 UNION ALL --SELECT 29 UNION ALL --SELECT 39 UNION ALL --SELECT 40 UNION ALL --SELECT 56 DECLARE @RowAsc TABLE ( ID INT IDENTITY, Amount INT ) INSERT INTO @RowAsc SELECT VALUE FROM @testTable ORDER BY VALUE ASC SELECT AVG(amount) FROM @RowAsc ra WHERE ra.id IN ( SELECT ID FROM @RowAsc WHERE ra.id - ( SELECT MAX(id) / 2.0 FROM @RowAsc ) BETWEEN 0 AND 1 )


Hay muchas formas de hacerlo, con un rendimiento espectacularmente variable. Aquí hay una solución particularmente bien optimizada, de Medians, ROW_NUMBER y rendimiento . Esta es una solución particularmente óptima cuando se trata de E / S reales generadas durante la ejecución: parece más costosa que otras soluciones, pero en realidad es mucho más rápida.

Esa página también contiene una discusión de otras soluciones y detalles de pruebas de rendimiento. Tenga en cuenta el uso de una columna única como desambiguador en caso de que haya múltiples filas con el mismo valor de la columna mediana.

Al igual que con todos los escenarios de rendimiento de la base de datos, siempre intente probar una solución con datos reales en hardware real. Nunca se sabe cuándo un cambio en el optimizador de SQL Server o una peculiaridad en su entorno hará que una solución normalmente rápida sea más lenta.

SELECT CustomerId, AVG(TotalDue) FROM ( SELECT CustomerId, TotalDue, -- SalesOrderId in the ORDER BY is a disambiguator to break ties ROW_NUMBER() OVER ( PARTITION BY CustomerId ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc, ROW_NUMBER() OVER ( PARTITION BY CustomerId ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc FROM Sales.SalesOrderHeader SOH ) x WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1) GROUP BY CustomerId ORDER BY CustomerId;


Intento con varias alternativas, pero debido a que mis registros de datos tienen valores repetidos, las versiones ROW_NUMBER parecen no ser una opción para mí. Entonces aquí la consulta que utilicé (una versión con NTILE):

SELECT distinct CustomerId, ( MAX(CASE WHEN Percent50_Asc=1 THEN TotalDue END) OVER (PARTITION BY CustomerId) + MIN(CASE WHEN Percent50_desc=1 THEN TotalDue END) OVER (PARTITION BY CustomerId) )/2 MEDIAN FROM ( SELECT CustomerId, TotalDue, NTILE(2) OVER ( PARTITION BY CustomerId ORDER BY TotalDue ASC) AS Percent50_Asc, NTILE(2) OVER ( PARTITION BY CustomerId ORDER BY TotalDue DESC) AS Percent50_desc FROM Sales.SalesOrderHeader SOH ) x ORDER BY CustomerId;


La siguiente consulta devuelve la mediana de una lista de valores en una columna. No se puede usar como una función agregada o junto con ella, pero puede usarla como una sub consulta con una cláusula WHERE en la selección interna.

SQL Server 2005+:

SELECT TOP 1 value from ( SELECT TOP 50 PERCENT value FROM table_name ORDER BY value )for_median ORDER BY value DESC


La siguiente solución funciona bajo estas suposiciones:

  • Sin valores duplicados
  • Sin valores NULL

Código:

IF OBJECT_ID(''dbo.R'', ''U'') IS NOT NULL DROP TABLE dbo.R CREATE TABLE R ( A FLOAT NOT NULL); INSERT INTO R VALUES (1); INSERT INTO R VALUES (2); INSERT INTO R VALUES (3); INSERT INTO R VALUES (4); INSERT INTO R VALUES (5); INSERT INTO R VALUES (6); -- Returns Median(R) select SUM(A) / CAST(COUNT(A) AS FLOAT) from R R1 where ((select count(A) from R R2 where R1.A > R2.A) = (select count(A) from R R2 where R1.A < R2.A)) OR ((select count(A) from R R2 where R1.A > R2.A) + 1 = (select count(A) from R R2 where R1.A < R2.A)) OR ((select count(A) from R R2 where R1.A > R2.A) = (select count(A) from R R2 where R1.A < R2.A) + 1) ;



Mi respuesta rápida original fue:

select max(my_column) as [my_column], quartile from (select my_column, ntile(4) over (order by my_column) as [quartile] from my_table) i --where quartile = 2 group by quartile

Esto le dará la mediana y el rango intercuartílico de una sola vez. Si realmente solo quieres una fila que sea la mediana, entonces quita el comentario de la cláusula where.

Cuando lo incluyes en un plan de explicación, el 60% del trabajo consiste en ordenar los datos que son inevitables al calcular estadísticas dependientes de la posición como esta.

He enmendado la respuesta para seguir la excelente sugerencia de Robert Ševčík-Robajz en los comentarios a continuación:

;with PartitionedData as (select my_column, ntile(10) over (order by my_column) as [percentile] from my_table), MinimaAndMaxima as (select min(my_column) as [low], max(my_column) as [high], percentile from PartitionedData group by percentile) select case when b.percentile = 10 then cast(b.high as decimal(18,2)) else cast((a.low + b.high) as decimal(18,2)) / 2 end as [value], --b.high, a.low, b.percentile from MinimaAndMaxima a join MinimaAndMaxima b on (a.percentile -1 = b.percentile) or (a.percentile = 10 and b.percentile = 10) --where b.percentile = 5

Esto debería calcular los valores medianos y percentiles correctos cuando tiene un número par de elementos de datos. Nuevamente, elimine el comentario de la cláusula where final si solo desea la mediana y no toda la distribución de percentiles.


Para conjuntos de datos a gran escala, puede probar este GIST:

https://gist.github.com/chrisknoll/1b38761ce8c5016ec5b2

Funciona agregando los distintos valores que encontraría en su conjunto (como las edades, el año de nacimiento, etc.) y utiliza las funciones de la ventana SQL para localizar cualquier posición de percentil que especifique en la consulta.


Para principiantes como yo, que están aprendiendo los conceptos básicos, personalmente encuentro este ejemplo más fácil de seguir, ya que es más fácil entender exactamente qué está sucediendo y de dónde vienen los valores medianos ...

select ( max(a.[Value1]) + min(a.[Value1]) ) / 2 as [Median Value1] ,( max(a.[Value2]) + min(a.[Value2]) ) / 2 as [Median Value2] from (select datediff(dd,startdate,enddate) as [Value1] ,xxxxxxxxxxxxxx as [Value2] from dbo.table1 )a

¡En absoluto temor de algunos de los códigos anteriores!


Para su pregunta, Jeff Atwood ya había dado la solución simple y efectiva. Pero, si está buscando algún método alternativo para calcular la mediana, el siguiente código SQL lo ayudará.

create table employees(salary int); insert into employees values(8); insert into employees values(23); insert into employees values(45); insert into employees values(123); insert into employees values(93); insert into employees values(2342); insert into employees values(2238); select * from employees; declare @odd_even int; declare @cnt int; declare @middle_no int; set @cnt=(select count(*) from employees); set @middle_no=(@cnt/2)+1; select @odd_even=case when (@cnt%2=0) THEN -1 ELse 0 END ; select AVG(tbl.salary) from (select salary,ROW_NUMBER() over (order by salary) as rno from employees group by salary) tbl where tbl.rno=@middle_no or tbl.rno=@middle_no+@odd_even;

Si está buscando calcular la mediana en MySQL, este enlace github será útil.


Para una variable / medida continua ''col1'' de ''tabla1''

select col1 from (select top 50 percent col1, ROW_NUMBER() OVER(ORDER BY col1 ASC) AS Rowa, ROW_NUMBER() OVER(ORDER BY col1 DESC) AS Rowd from table1 ) tmp where tmp.Rowa = tmp.Rowd


Quería encontrar una solución por mí mismo, pero mi cerebro tropezó y cayó en el camino. Creo que funciona, pero no me pidas que lo explique por la mañana. :PAG

DECLARE @table AS TABLE ( Number int not null ); insert into @table select 2; insert into @table select 4; insert into @table select 9; insert into @table select 15; insert into @table select 22; insert into @table select 26; insert into @table select 37; insert into @table select 49; DECLARE @Count AS INT SELECT @Count = COUNT(*) FROM @table; WITH MyResults(RowNo, Number) AS ( SELECT RowNo, Number FROM (SELECT ROW_NUMBER() OVER (ORDER BY Number) AS RowNo, Number FROM @table) AS Foo ) SELECT AVG(Number) FROM MyResults WHERE RowNo = (@Count+1)/2 OR RowNo = ((@Count+1)%2) * ((@Count+2)/2)


Si desea usar la función Create Aggregate en SQL Server, esta es la forma de hacerlo. Hacerlo de esta manera tiene la ventaja de poder escribir consultas claras. Tenga en cuenta que este proceso podría adaptarse para calcular un valor Percentil con bastante facilidad.

Cree un nuevo proyecto de Visual Studio y establezca el marco de destino en .NET 3.5 (esto es para SQL 2008, puede ser diferente en SQL 2012). A continuación, cree un archivo de clase y coloque el siguiente código, o c # equivalente:

Imports Microsoft.SqlServer.Server Imports System.Data.SqlTypes Imports System.IO <Serializable> <SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, _ IsInvariantToOrder:=True, MaxByteSize:=-1, IsNullIfEmpty:=True)> Public Class Median Implements IBinarySerialize Private _items As List(Of Decimal) Public Sub Init() _items = New List(Of Decimal)() End Sub Public Sub Accumulate(value As SqlDecimal) If Not value.IsNull Then _items.Add(value.Value) End If End Sub Public Sub Merge(other As Median) If other._items IsNot Nothing Then _items.AddRange(other._items) End If End Sub Public Function Terminate() As SqlDecimal If _items.Count <> 0 Then Dim result As Decimal _items = _items.OrderBy(Function(i) i).ToList() If _items.Count Mod 2 = 0 Then result = ((_items((_items.Count / 2) - 1)) + (_items(_items.Count / 2))) / 2@ Else result = _items((_items.Count - 1) / 2) End If Return New SqlDecimal(result) Else Return New SqlDecimal() End If End Function Public Sub Read(r As BinaryReader) Implements IBinarySerialize.Read ''deserialize it from a string Dim list = r.ReadString() _items = New List(Of Decimal) For Each value In list.Split(","c) Dim number As Decimal If Decimal.TryParse(value, number) Then _items.Add(number) End If Next End Sub Public Sub Write(w As BinaryWriter) Implements IBinarySerialize.Write ''serialize the list to a string Dim list = "" For Each item In _items If list <> "" Then list += "," End If list += item.ToString() Next w.Write(list) End Sub End Class

A continuación, compílelo y copie el archivo DLL y PDB en su máquina SQL Server y ejecute el siguiente comando en SQL Server:

CREATE ASSEMBLY CustomAggregate FROM ''{path to your DLL}'' WITH PERMISSION_SET=SAFE; GO CREATE AGGREGATE Median(@value decimal(9, 3)) RETURNS decimal(9, 3) EXTERNAL NAME [CustomAggregate].[{namespace of your DLL}.Median]; GO

A continuación, puede escribir una consulta para calcular la mediana de esta manera: SELECCIONE dbo.Median (campo) FROM Table


Si está usando SQL 2005 o mejor, este es un cálculo de mediana simple y simple para una sola columna en una tabla:

SELECT ( (SELECT MAX(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf) + (SELECT MIN(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf) ) / 2 AS Median


Simple, rápido y preciso

SELECT x.Amount FROM (SELECT amount, Count(1) OVER (partition BY ''A'') AS TotalRows, Row_number() OVER (ORDER BY Amount ASC) AS AmountOrder FROM facttransaction ft) x WHERE x.AmountOrder = Round(x.TotalRows / 2.0, 0)


Sobre la base de la respuesta de Jeff Atwood aquí arriba, es con GROUP BY y una subconsulta correlacionada para obtener la mediana para cada grupo.

SELECT TestID, ( (SELECT MAX(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts WHERE TestID = Posts_parent.TestID ORDER BY Score) AS BottomHalf) + (SELECT MIN(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts WHERE TestID = Posts_parent.TestID ORDER BY Score DESC) AS TopHalf) ) / 2 AS MedianScore, AVG(Score) AS AvgScore, MIN(Score) AS MinScore, MAX(Score) AS MaxScore FROM Posts_parent GROUP BY Posts_parent.TestID



--Create Temp Table to Store Results in DECLARE @results AS TABLE ( [Month] datetime not null ,[Median] int not null ); --This variable will determine the date DECLARE @IntDate as int set @IntDate = -13 WHILE (@IntDate < 0) BEGIN --Create Temp Table DECLARE @table AS TABLE ( [Rank] int not null ,[Days Open] int not null ); --Insert records into Temp Table insert into @table SELECT rank() OVER (ORDER BY DATEADD(mm, DATEDIFF(mm, 0, DATEADD(ss, SVR.close_date, ''1970'')), 0), DATEDIFF(day,DATEADD(ss, SVR.open_date, ''1970''),DATEADD(ss, SVR.close_date, ''1970'')),[SVR].[ref_num]) as [Rank] ,DATEDIFF(day,DATEADD(ss, SVR.open_date, ''1970''),DATEADD(ss, SVR.close_date, ''1970'')) as [Days Open] FROM mdbrpt.dbo.View_Request SVR LEFT OUTER JOIN dbo.dtv_apps_systems vapp on SVR.category = vapp.persid LEFT OUTER JOIN dbo.prob_ctg pctg on SVR.category = pctg.persid Left Outer Join [mdbrpt].[dbo].[rootcause] as [Root Cause] on [SVR].[rootcause]=[Root Cause].[id] Left Outer Join [mdbrpt].[dbo].[cr_stat] as [Status] on [SVR].[status]=[Status].[code] LEFT OUTER JOIN [mdbrpt].[dbo].[net_res] as [net] on [net].[id]=SVR.[affected_rc] WHERE SVR.Type IN (''P'') AND SVR.close_date IS NOT NULL AND [Status].[SYM] = ''Closed'' AND SVR.parent is null AND [Root Cause].[sym] in ( ''RC - Application'',''RC - Hardware'', ''RC - Operational'', ''RC - Unknown'') AND ( [vapp].[appl_name] in (''3PI'',''Billing Rpts/Files'',''Collabrent'',''Reports'',''STMS'',''STMS 2'',''Telco'',''Comergent'',''OOM'',''C3-BAU'',''C3-DD'',''DIRECTV'',''DIRECTV Sales'',''DIRECTV Self Care'',''Dealer Website'',''EI Servlet'',''Enterprise Integration'',''ET'',''ICAN'',''ODS'',''SB-SCM'',''SeeBeyond'',''Digital Dashboard'',''IVR'',''OMS'',''Order Services'',''Retail Services'',''OSCAR'',''SAP'',''CTI'',''RIO'',''RIO Call Center'',''RIO Field Services'',''FSS-RIO3'',''TAOS'',''TCS'') OR pctg.sym in (''Systems.Release Health Dashboard.Problem'',''DTV QA Test.Enterprise Release.Deferred Defect Log'') AND [Net].[nr_desc] in (''3PI'',''Billing Rpts/Files'',''Collabrent'',''Reports'',''STMS'',''STMS 2'',''Telco'',''Comergent'',''OOM'',''C3-BAU'',''C3-DD'',''DIRECTV'',''DIRECTV Sales'',''DIRECTV Self Care'',''Dealer Website'',''EI Servlet'',''Enterprise Integration'',''ET'',''ICAN'',''ODS'',''SB-SCM'',''SeeBeyond'',''Digital Dashboard'',''IVR'',''OMS'',''Order Services'',''Retail Services'',''OSCAR'',''SAP'',''CTI'',''RIO'',''RIO Call Center'',''RIO Field Services'',''FSS-RIO3'',''TAOS'',''TCS'') ) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(ss, SVR.close_date, ''1970'')), 0) = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,@IntDate,getdate())), 0) ORDER BY [Days Open] DECLARE @Count AS INT SELECT @Count = COUNT(*) FROM @table; WITH MyResults(RowNo, [Days Open]) AS ( SELECT RowNo, [Days Open] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Days Open]) AS RowNo, [Days Open] FROM @table) AS Foo ) insert into @results SELECT DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,@IntDate,getdate())), 0) as [Month] ,AVG([Days Open])as [Median] FROM MyResults WHERE RowNo = (@Count+1)/2 OR RowNo = ((@Count+1)%2) * ((@Count+2)/2) set @IntDate = @IntDate+1 DELETE FROM @table END select * from @results order by [Month]


DECLARE @Obs int DECLARE @RowAsc table ( ID INT IDENTITY, Observation FLOAT ) INSERT INTO @RowAsc SELECT Observations FROM MyTable ORDER BY 1 SELECT @Obs=COUNT(*)/2 FROM @RowAsc SELECT Observation AS Median FROM @RowAsc WHERE ID=@Obs