1

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?

1
  • @joe , no, it is not duplicate,please check my post, i edited it Commented Jul 2, 2018 at 16:37

1 Answer 1

3

You are looking for PERCENTILE_DISC or PERCENTILE_CONT:

E.g.

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...) as median
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.