SQLTeam.com | Weblogs | Forums

Partitioned Median


#1

Hi,

I wonder if anyone can help I am trying to calculate a median which abides by the groupings in my view.

I like the following code but it just gives me the over all median in each row. I think i need to use OVER(PARTITION BY()) but i just can't make heads nor tails of it even after furious googling and reading renowned articles like this https://sqlperformance.com/2012/08/t-sql-queries/median

SELECT
YEAR(reportsubmitted) as "Year Submitted",
Month(reportsubmitted) as "Month Submitted", COUNT (DISTINCT(propertyid)) as "Number of Reports Submitted", SUM([report fee]) as "Total Report Fee",

(
(SELECT MAX([days From Audit to Submission])

FROM (SELECT TOP 50 PERCENT ([days From Audit to Submission] )

FROM vwCMnAuditorsProcessLength WHERE ReportSubmitted > '2017-04-01' ORDER BY [days From Audit to Submission] ) AS x)

(SELECT MIN([days From Audit to Submission])

FROM (SELECT TOP 50 PERCENT [days From Audit to Submission]
FROM vwCMnAuditorsProcessLength WHERE ReportSubmitted > '2017-04-01' ORDER BY [Report Fee] DESC) AS y)
) / 2.0 as "Median Days"

FROM vwCMnAuditorsProcessLength
WHERE reportsubmitted >= '2017-04-01'

GROUP BY MONTH(reportsubmitted), YEAR(reportsubmitted)

I did try the below as something different but it seems to be discounting a lot of data

SELECT

[MMYYYY ReportSubmitted],

[Total Report Fee],

[Number of Reports Submitted],

AVG([days from audit to submission]) as "Median days to Submission"

FROM (

SELECT [MMYYYY ReportSubmitted], [report fee], propertyid,
CAST([days from audit to submission] as decimal(5,2)) [days from audit to submission],

ROW_NUMBER() OVER(
Partition by [MMYYYY ReportSubmitted]
Order by [days from audit to submission] ASC) AS "RowASC",

ROW_NUMBER() OVER(
Partition by [MMYYYY ReportSubmitted]
Order by [days from audit to submission] DESC) AS "RowDESC",

SUM([report fee]) OVER(Partition by [MMYYYY ReportSubmitted] Order by [days from audit to submission]) AS "Total Report Fee",
COUNT(propertyid) OVER(Partition by [MMYYYY ReportSubmitted] Order by [days from audit to submission]) AS "Number of Reports Submitted"

FROM vwCMnAuditorsProcessLength) x

WHERE RowASC in (RowDESC,RowDESC-1,RowDESC+1)

Group by [MMYYYY ReportSubmitted], [Total Report Fee], [Number of Reports Submitted]
Order by [MMYYYY ReportSubmitted]

If anyone has any ideas I would be really greatful


#2

Have a look at this