Median instead avg() in SQL Server 2012

Question

I have this query

;WITH cte AS
(
    SELECT
        *,
        DATEPART(WEEKDAY, Dt) AS WeekDay,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) OVER (PARTITION BY CustomerType, [CustomerName], ItemRelation, DocumentNum, DocumentYear) AS PERCENTILE,
        AVG(SaleCount) OVER (PARTITION BY CustomerType, [CustomerName], ItemRelation, DocumentNum, DocumentYear, DATEPART(WEEKDAY, Dt), IsPromo) AS AVG_WeekDay
    FROM
        promo_data_copy
)
UPDATE a 
SET SaleCount = cte.AVG_WeekDay
FROM CTE
JOIN promo_data_copy a ON a.Dt = cte.dt
                       AND a.ItemRelation = cte.ItemRelation 
                       AND a.CustomerName = cte.CustomerName
                       AND a.DocumentNum = cte.DocumentNum 
                       AND a.DocumentYear = cte.DocumentYear 
                       AND a.CustomerType = cte.CustomerType 
                       AND a.ispromo = cte.ispromo
WHERE 
    CTE.PERCENTILE < CTE.SaleCount
    AND DATEPART(WEEKDAY, CTE.Dt) < 7
    AND CTE.ispromo = 0 ;

Here there is string

avg(SaleCount) over (Partition by CustomerType, [CustomerName], ItemRelation, DocumentNum, DocumentYear,datePart(WEEKDAY,Dt), IsPromo) as AVG_WeekDay
    From promo_data_copy)

How can I calculate the median instead of avg()? This query replaces outliers by mean value.

I need replace it by median, so the string above must be instead of avg().

Contain median (but there is no median function in T-SQL like in Excel)

Can anybody help please?

Answer 1

You are looking for PERCENTILE_DISC or PERCENTILE_CONT:

• https://docs.microsoft.com/en-us/sql/t-sql/functions/percentile-disc-transact-sql?view=sql-server-2017

E.g.

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...) as median