How to calculate median for multiple columns in SQL Server

  • Hi,

    I have a below query. Instead of average I need to calculate median for the below query. Could you please help on this.

    ;With Resultaverageduration As (

    select a,

     

    avg(CASE WHEN   IS NULL or = '01/01/1970'

    THEN  0  end) As [NS],

     

    avg(CASE WHEN c IS not NULL and c != '01/01/1970'

    THEN  DateDiff(day,dateadd(d,1,[closing date]),c) else 0  end)  As [SA] ,

     

    avg(CASE WHEN [d] IS not NULL

    Then  DATEDIFF(day, c,d) else 0 end) as [RR],

     

    avg(CASE WHEN [ e] IS not NULL and   THEN

    DATEDIFF(day,d,e) else 0 end)  as [RS],

     

    avg(CASE WHEN [f] IS not NULL and  THEN

    DATEDIFF(day,d,f) else 0 end)  as  [Rc]

     

     

     

     

    from

    group by a

    )

    Insert into [Table]

    select a,Stage,[NO of Days] from (

     

    SELECT a,

    0 AS [NS],

    cast(AVG (ISNULL([SA], 0)) as Int)  AS [SA],

    cast(AVG (ISNULL([RR], 0))  as Int) AS [RR] ,

    cast(AVG (ISNULL([RS], 0)) as Int) AS [RS],

    cast(AVG (ISNULL([RC], 0)) as Int) AS [RC]

    FROM  Resultaverageduration

    group by a) Results1

     

    unpivot

    ([No of Days]

    for Stage in ([NS],

    [SA],

    [RR],

    [RS],

    [RC]))Ap2;

  • Check out Aaron Bertrand's article: What is the fastest way to calculate the median?

     

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply