Max Date query

1

Hi,

This query returns all the testing dates for item no's where the testing date is older than 180 days. But I only want to return the record if it's been more than 180 days for the most recent testing date. Your help is greatly appreciated. Thank you.

select a.assignno, a.itemno, a.serno ,a.oan, a.description, a.brand, a.modelno,c.tested--,datediff(day,getdate(), c.tested)
from pp_invt a
inner join training.dbo.armshist c on a.serno = c.SERNO
where a.CATEGORYNO = 'ECSO09PRC00000049'
and a.ASSIGNNO = 'ECSO09PER000147'
and datediff(day,getdate(), c.tested) < -180

2

Something like this?

 Select a.assignno
      , a.itemno
      , a.serno
      , a.oan
      , a.description
      , a.brand
      , a.modelno
      , MostRecent = max(c.tested)  --,datediff(day,getdate(), c.tested)
   From pp_invt a
  Inner Join training.dbo.armshist c On a.serno = c.SERNO
  Where a.CATEGORYNO = 'ECSO09PRC00000049'
    And a.ASSIGNNO = 'ECSO09PER000147'
  Group By
        a.assignno
      , a.itemno
      , a.serno
      , a.oan
      , a.description
      , a.brand
      , a.modelno
 Having max(tested) < dateadd(day, -180, getdate());
3

That's it! Thanks so much Jeff.