T-SQL: WHERE clause in OVER(PARTITION BY... ORDER BY...)

Question

I have the following query

;WITH tmp AS
(
    SELECT *, ROW_NUMBER()
    OVER
        (PARTITION BY to_tel, duration, call_date 
        ORDER BY rates_start DESC) as rn
    FROM ##TempTable
)
SELECT *
FROM tmp
WHERE rn = 1
ORDER BY customer_id, to_code, duration

But I would like to modify it where it doesn't give me the maximum rates_start, but the maximum rates_start before a certain date. Is there any way I can do this?

Answer 1

You can add WHERE inside the cte part. I'm not sure if you still want to partition by call_date in this case (I removed it). Change the PARTITION BY part if needed.

;WITH tmp AS
(
    SELECT *, ROW_NUMBER()
    OVER
        (PARTITION BY to_tel, duration
        ORDER BY rates_start DESC) as rn
    FROM ##TempTable
    WHERE call_date < @somedate
)
SELECT *
FROM tmp
WHERE rn = 1
ORDER BY customer_id, to_code, duration