IN clause in select query with a subquery

I have a query where I join multiple tables. I would like to filter the results based on the status codes and I also want to get the records that were most recently created. Is the below SQL correct? Meaning – can I use IN clause for the statuscodes, or should it be three different queries, one for each status?

SELECT
         A.DetailID
       , H.PersonID
       , A.RateAmount
       , A.Code
       , A.TypeCode
FROM
         CMPN.Detail       A
       , CMPN.DetailStatus B
       , CMPN.Header       H
WHERE
         H.PayGroup IN ('A Pay Group','B Pay Group','C Pay Group')
         AND A.EndDate = '2020-02-10 06:00:00.0000000 +00:00'
         AND A.LastUpdateTimestamp      <= '2020-02-24 06:00:00.0000000 +00:00'
         AND A.DetailID = B.DetailID
         AND H.HeaderId = A.HeaderId
         AND **B.DetailStatusCode IN ('Approved','Completed','Pending')**
         AND B.CreateTimestamp =
         (
                SELECT
                       MAX(C.CreateTimestamp)
                FROM
                       CMPN.DetailStatus C
                WHERE
                       A.DetailID = C.DetailID AND **C.DetailStatusCode IN ('Approved','Completed','Pending'**)
         )
ORDER BY
         H.PersonID
       , A.Code
       , A.TypeCode

Answer

It’s not perfectly clear what you’re after (see my comments on the question). But my best guess is you can do this with an APPLY:

SELECT d.DetailID, h.PersonID, d.RateAmount, d.Code, d.TypeCode
    , s.DetailStatusCode AS [Status], s.CreateTimeStamp As [StatusDate]
FROM CMPN.Detail d
CROSS APPLY ( -- change this to OUTER APPLY if you need it to behave more like a LEFT JOIN
    SELECT TOP 1 DetailSatusCode, CreateTimeStamp 
    FROM CMPN.DetailStatus ds 
    WHERE ds.DetailID = d.DetailID
        AND ds.DetailStatusCode IN ('Approved','Completed','Pending')
    ORDER BY ds.CreateTimeStamp DESC
) s
INNER JOIN CMPN.Header h ON h.HeaderID = d.HeaderID 
    AND h.PayGroup IN ('A Pay Group','B Pay Group','C Pay Group')
WHERE  d.EndDate = '2020-02-10 06:00:00.0000000 +00:00'
    AND d.LastUpdateTimestamp <= '2020-02-24 06:00:00.0000000 +00:00'
ORDER BY h.PersonID, d.Code, d.TypeCode

APPLY is like a correlated subquery/derived table in the SELECT clause, but it can be faster when you might have needed to use multiple subqueries against the same table. This allows us to only visit that table once to get both the DetailStatusCode and CreateTimeStamp columns. In Oracle and Postgresql this is called a LATERAL JOIN. These APPLY/Lateral join operations do not mix well with the old obsolete “A,B” join syntax, and is just one of many reasons not to use that old syntax.

Often the ROW_NUMBER() windowing function can also be used for even better performance, with a condition where the row number equals 1.

Note there was initially no meaningful JOIN condition for the Header table. The explicit JOIN syntax helps make this kind of error more obvious. I also had to infer what fields you needed from DetailStatus. As it was, the table served no purpose in the original query.