Return all archive records when any one record meets criteria

0 votes

1 answer

55 views

                          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-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:

Asked by Kim (11 rep)

Nov 11, 2021, 10:50 PM
Last activity: Nov 14, 2021, 02:03 PM

Return all archive records when any one record meets criteria

Related Questions