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?
