[SQL] Subquery

Example:

select 
    convert(varchar(10), DATEADD(DAY, -1, GETDATE()), 120) AS [...],
    SUM(CASE WHEN [C1] = ... THEN 1 ELSE 0 END) AS [...],
    SUM(CASE WHEN [C1] = ... THEN 1 ELSE 0 END) AS [...],
    SUM(CASE WHEN [C1] = ... THEN 1 ELSE 0 END) AS [...],
from T where [D] in 
(
    select max([D]) from ...
    where
        [D] >= convert(varchar(10), DATEADD(DAY, 0, GETDATE()), 120)
        and [D] < convert(varchar(10), DATEADD(DAY, -1, GETDATE()), 120)
    group by [C2]
)