SQL Server: Query Efficiency Having vs Subquery I have the following query where I used a sub query since I can't use aggregates in the where clause.

SSMS suggested I use the HAVING clause but then I would need to group by all the other columns (right?) which I did not want to have to do so I ended up using the sub query.

Is using the sub query the most efficient way or should I use the Having clause so I don't have to read from the same table twice? 

Is the difference negligible?

select *
from [DW_STAGE].[ETL].[Claims]
where pd_dt &gt; (
        select dateadd(m, - 12, cast(max(pd_dt) as date))
        from [DW_STAGE].[ETL].[Claims]
        )
If your table's clustered index has pd_dt as the first column, this may be the most optimal query. You could simplify it by breaking it into two parts, like so:

DECLARE @dt date;
SELECT @dt=DATEADD(month, -12, CAST(MAX(pd_dt) AS date)) FROM DW_STAGE.ETL.Claims;

SELECT * FROM DW_STAGE.ETL.Claims WHERE pd_dt&gt;@dt;


Things you could do to improve performance:


Reduce the number of columns from * to those specific columns that you need.
Create a covering, non-clustered index for these columns, where the first column of the index is pd_dt or pd_dt DESC.