Searching from the latest date in SQL Server
For starters, don't use @DealClauseString - just have your query.

Also, remove ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - when you're using the whole partition, there's no reason to have the ORDER BY clause at all.

Anyway - for your particular question, if I understand it correctly, you want it to be like your #t table only has records that are at least a month old. So you should put a WHERE clause in the filter CTE. So it becomes something like:

FROM #t
WHERE D &lt;= DATEADD(MONTH,-1,SYSDATETIME())