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

enter image description here

My query so far:

SELECT PTA.PlnndTrnsfrID, PTA.PlnndTrnsfrArchveID, PTA.FromDate, PTA.ToDate
FROM [Support60].[dbo].PlannedTransferArchive PTA
       (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

enter image description here

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: enter image description here

But not these:

enter image description here


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

    pta.PlnndTrnsfrID, pta.PlnndTrnsfrArchveID, pta.FromDate, pta.ToDate
    [Support60].[dbo].PlannedTransferArchive AS pta
     ( 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'
                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'
    pta.FromDate DESC ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.