Below are 3 lines of code in my SQL SELECT statement that calculate the 25th, 50th and 75th percentile values over column [Value]. I need to modify this logic so that the 25th and 75th percentile values are calculated as follows:
For 25th percentile, I use PERCENTILE_CONT(0.5) on all rows in [Value] that are smaller than [Median]
For 75th percentile, I use PERCENTILE_CONT(0.5) on all rows in [Value] that are greater than [Median]
,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY A.[Value]) OVER (PARTITION BY A.[DateSK],A.[Metric]) AS [25th]
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY A.[Value]) OVER (PARTITION BY A.[DateSK],A.[Metric]) AS [Median]
,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY A.[Value]) OVER (PARTITION BY A.[DateSK],A.[Metric]) AS [75th]
Unable to figure out how I can utilize the WHERE clause to only apply to these columns.
