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: