Return all archive records when any one record meets criteria

Question

I want to get all archive records related a transfer ID where at least one archive record has a date in the report month (in this case September).

The Archive Table

    SELECT PTA.PlnndTrnsfrID, PTA.PlnndTrnsfrArchveID, PTA.fromdate, PTA.ToDate
    
FROM [Support60].[dbo].PlannedTransferArchive PTA

My query so far:

SELECT PTA.PlnndTrnsfrID, PTA.PlnndTrnsfrArchveID, PTA.FromDate, PTA.ToDate
FROM [Support60].[dbo].PlannedTransferArchive PTA
 
 WHERE EXISTS
       (SELECT PTAA.PlnndTrnsfrID
          FROM [Support60].[dbo].PlannedTransferArchive PTAA
         WHERE PTAA.PlnndTrnsfrID = PTA.PlnndTrnsfrID
           AND PTA.FromDAte >= '2021-09-01 00:00:00' and PTA.Fromdate <= '2021-09-30 23:59:00' or 
           PTA.PlnndTrnsfrID = PTAA.PlnndTrnsfrID and PTA.ToDate >= '2021-09-01 00:00:00' and PTA.ToDate <= '2021-09-30 23:59:00')

Order by pta.FromDate desc

Additionally, Thank you ypercube, the condition checking the main instead of sub was my problem. I still have a problem with the dates. I need any record where the from or to date is any date in September. For Example, I want to capture these records in the subquery:

But not these:

Answer 1

The WHERE clause in the subquery is not correct:

• the condition should be checking the subquery table (PTAA), not the main one (PTA), in order to get the correlated effect you are seeking.
• minor correction/improvement: simpler to use inclusion-exclusion ranges for dates/datetime. It looks like you want to check if the range (From - To) overlaps with September. We only need two checks for intervals overlap, i.e. (fromA < toB AND fromB < toA)

The query becomes:

SELECT 
    pta.PlnndTrnsfrID, pta.PlnndTrnsfrArchveID, pta.FromDate, pta.ToDate
  FROM 
    [Support60].[dbo].PlannedTransferArchive AS pta
 WHERE 
     EXISTS
     ( SELECT ptaa.PlnndTrnsfrID
         FROM [Support60].[dbo].PlannedTransferArchive AS ptaa
        WHERE ptaa.PlnndTrnsfrID = pta.PlnndTrnsfrID
        -- check if FromDate or ToDate is in September
          AND ( ptaa.Fromdate >= '2021-09-01'
            AND ptaa.Fromdate  < '2021-10-01'
             OR
                ptaa.ToDate >= '2021-09-01'
            AND ptaa.ToDate  < '2021-10-01'
              )
        -- check for interval overlap with September 2021
        --  AND '2021-09-01'  < ptaa.ToDate   
        --  AND ptaa.Fromdate < '2021-10-01'
       )
ORDER BY
    pta.FromDate DESC ;