Statistics updating SQL Query

Question

I have the following SQL statement that I think could be improved in areas (I believe there may be a way to use where over having, but I'm not sure how and I'm sure there's a way to reference the last updated column in the having clause but I'm unsure how to do it). Any suggestions would be greatly appreciated:

/* Show indexes that haven't had statistics updated in two days or more */
select  
        t.name as [Table Name], 
        min(convert(CHAR(11),stats_date(t.object_id,s.[stats_id]),106)) as [Last Updated]
from    sys.[stats] as s
        inner join sys.[tables] as t
            on [s].[object_id] = [t].[object_id]
group by t.name
having  min(convert(CHAR(11),stats_date(t.object_id,s.[stats_id]),106)) < dateadd(day, -1, getdate())
order by 2 desc

Answer 1

You can just use the condition in a where, that will filter the single records instead of filtering groups, then you can group the result to avoid duplicates in the result.

You shouldn't convert the date to a string when you are comparing it. When you convert it to a string, you should do that after getting the lowest value, otherwise min will compare the dates as strings and give you the wrong result.

select
  t.name as [Table Name], 
  convert(CHAR(11),min(stats_date(t.object_id,s.[stats_id])),106) as [Last Updated]
from
  sys.[stats] as s
  inner join sys.[tables] as t on s.[object_id] = t.[object_id]
where
  stats_date(t.object_id,s.[stats_id]) < dateadd(day, -1, getdate())
group by
  t.name
order by
  2 desc