how to fix an error : Windowed functions cannot be used in the context of another windowed function or aggregate.

Question

below is the query :

DECLARE @InputValue INT = 840;

SELECT
    @InputValue AS InputValue,
    SUM((CASE 
            WHEN @InputValue >= PRRangeUpto THEN (LEAD(PRRangeUpto, 1, @InputValue) OVER (ORDER BY PRRangeUpto) - PRRangeUpto) * PRRate
            ELSE 0
        END)) AS TotalAmount
FROM
    PRRateRange
WHERE
    @InputValue >= PRRangeUpto;

this query gives an error : Windowed functions cannot be used in the context of another windowed function .How to fix it?

Answer 1

Try something like this:

;
with Q as
(
    select *, lead(PRRangeUpto, 1, @InputValue) over (order by PRRangeUpto) as ld
    from PRRateRange
)
select @InputValue AS InputValue, sum(case when @InputValue >= PRRangeUpto then ld * PRRate end) as TotalAmount
from Q
where @InputValue >= PRRangeUpto

If it does not work, then show details to reproduce the problem.