How to calculate median for multiple columns in SQL Server

• Cindy

  SSC Enthusiast

  Points: 172

  More actions

  October 14, 2022 at 5:52 pm

  #4099733

  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;

• ratbak

  Default port

  Points: 1421

  More actions

  October 14, 2022 at 5:59 pm

  #4099734

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