Select rows only if having row number partition count equals to specific value

Question

I have a temporary table (from Stored Procedure) named TEMP1 as follows:

Rnk (Row Number Partition by DocNo)	DocNo	TransDate
1	Doc1	1 Aug 2023
2	Doc1	2 Aug 2023
3	Doc1	3 Aug 2023
1	Doc2	4 Aug 2023
2	Doc2	5 Aug 2023
1	Doc3	6 Aug 2023
2	Doc3	7 Aug 2023
3	Doc3	8 Aug 2023
1	Doc4	9 Aug 2023
2	Doc4	10 Aug 2023
3	Doc4	11 Aug 2023

I need to retrieve rows which have full Rnk (1 to 3) and TransDate for Rnk 3 is in a specific range.
For example, if I set TransDate range condition from 1 Aug 2023 to 10 Aug 2023, then it will return:

Rnk (Row Number Partition by DocNo)	DocNo	TransDate
1	Doc1	1 Aug 2023
2	Doc1	2 Aug 2023
3	Doc1	3 Aug 2023
1	Doc3	6 Aug 2023
2	Doc3	7 Aug 2023
3	Doc3	8 Aug 2023

How do I get these results?

Answer 1

One approach, using a CTE with conditional aggregation:

WITH cte AS (
    SELECT *, COUNT(CASE WHEN Rnk = 3 AND
                              TransDate BETWEEN '20230801' AND '20230810'
                         THEN 1 END) OVER (PARTITION BY DocNo) AS cnt
    FROM tempTable
)

SELECT Rnk, DocNo, TransDate
FROM cte
WHERE cnt > 0
ORDER BY DocNo, Rnk;

The cnt in the subquery will be greater than zero if there exist, for a given collection of documents in the same DocNo, a rank 3 record in the correct date range.