2
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ Don't you want max rather than min? Your comment at the top of the code suggest that you want tables whose latest statistics entry is more than two days ago, not whose earliest statistics entry is in that range. \$\endgroup\$ – Dawood ibn Kareem Feb 17 '12 at 8:09
2
\$\begingroup\$

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
|improve this answer|||||
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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