The With Statement in T-SQL – Percentile Calculation

Hi,

Those who are working with complex SQL might have found a need of using a common intermediate SUB-query result within the execution scope. You can use it with a SELECT, UPDATE, MERGE, INSERT or DELETE. This intermediate result-set is called Common Table Expression. It is even used in Creation of Views.

Here is an example:

WITH CET_TABLE ( EXPR_1) AS
{
SELECT FIELD_NAME  AS ALIAS_NAME
FROM PHY_TABLE
WHERE
CRITERIA_NAME = 201011
}
SELECT * FROM CET_TABLE

So here I have just created the an intermediate table that can be used in Select SQL as a usual table.

The main purpose I am using it is for percentile calculation in SQL-SERVER

DECLARE @percentile FLOAT
DECLARE @RESULT FLOAT
SELECT @percentile = .25;
WITH BAL_CALC(DIST_BAL) AS
(
SELECT
DISTINCT(Balance ) AS DIST_BAL
FROM
Data_table
where

Month= 201011
),emp_sal(DIST_BAL, prev_rank, curr_rank, next_rank) AS
(
SELECT DIST_BAL,
(ROW_NUMBER() OVER ( ORDER BY DIST_BAL ) – 2.0) / ((SELECT COUNT(*) FROM BAL_CALC) – 1) [prev_rank],
(ROW_NUMBER() OVER ( ORDER BY DIST_BAL ) – 1.0) / ((SELECT COUNT(*) FROM BAL_CALC) – 1) [curr_rank],
(ROW_NUMBER() OVER ( ORDER BY DIST_BAL ) + 0.0) / ((SELECT COUNT(*) FROM BAL_CALC) – 1) [next_rank]
FROM BAL_CALC
)
SELECT @RESULT = (SELECT
CASE
WHEN t1.DIST_BAL = t2.DIST_BAL THEN t1.DIST_BAL
ELSE t1.DIST_BAL + (t2.DIST_BAL – t1.DIST_BAL) * ((@percentile – t1.curr_rank) / (t2.curr_rank – t1.curr_rank))
END
FROM emp_sal t1, emp_sal t2
WHERE (t1.curr_rank = @percentile OR (t1.curr_rank < @percentile AND t1.next_rank > @percentile))
AND (t2.curr_rank = @percentile OR (t2.curr_rank > @percentile AND t2.prev_rank < @percentile)))