0

I have this as my Average Statement and am wanting to get the Median Value:

select AVG (DATEDIFF("m", dlg_applicationreceived, dlg_dateofdecision)) as TimetakenAVG, format(dlg_dateofdecision, 'MMM-yy') as MonthDate from dlg_ast group by format(dlg_dateofdecision, 'MMM-yy') order by format(dlg_dateofdecision, 'MMM-yy')

I've played around with some of the previous answers to Median Questions, but can't get mine to work correctly.

Any help would be most appreciated. Thanks

Tim Biegeleisen
  • 387,723
  • 20
  • 200
  • 263
Rae G
  • 1
  • 1
  • 1
    I voted to close as duplicate because the link has several methods you can try to compute the median. SQL Server, like most other databases, does not have a built in median function. – Tim Biegeleisen Aug 04 '17 at 05:42
  • Ok will keep trying, it's the DateDiff part that is complicating it. I cant see an answer using DateDiff and grouping. – Rae G Aug 04 '17 at 06:45
  • Try one of the methods and if you get stuck, then comment here and someone can reopen the question. – Tim Biegeleisen Aug 04 '17 at 06:54
  • I got this part to work with a Median result, but just one, I want to group it by the 'format(ass.dlg_dateofdecision, 'MM-yy') so that I have one Median result per month. SELECT x.MedianAnswer FROM (SELECT (DATEDIFF("m", ass.dlg_applicationreceived, ass.dlg_dateofdecision)) as ss, Count(1) OVER (partition BY 'A') AS TotalRows, Row_number() OVER (ORDER BY(DATEDIFF("m", ass.dlg_applicationreceived, ass.dlg_dateofdecision)) ASC) AS MedianAnswer FROM dlg_ass ass) x WHERE x.MedianAnswer = Round(x.TotalRows / 2.0, 0) – Rae G Aug 04 '17 at 07:03

0 Answers0