May 11, 2010 at 1:34 pm
I have a table called 'tracking' with two columns 'CollectionUrl' and 'LastAccessedDate'. I would like to identify only those records by CollectionUrl whose most recent date in 'LastAccessedDate' coulmn is more than 60 days old.
For example, here is the following data.
/site1/lib1 04/10/2010
/site1/lib1 05/01/2010
/site1/lib1 04/30/2010
/site2/lib2 03/01/2010
/site2/lib2 02/05/2010
/site2/lib2 01/22/2010
/site3/lib3 04/25/2010
/site3/lib3 05/05/2010
/site3/lib3 03/29/2010
/site4/lib4 03/02/2010
/site4/lib4 02/09/2010
/site4/lib4 02/21/2010
The query result should be
/site2/lib2 03/01/2010
/site4/lib4 03/02/2010
The query should retrieve at most one record per 'CollectionUrl' (with most recent 'LastAccessedDate') and that too only if it satisfies the condition of 'LastAccessedDate' is more than 60 days old.
May 11, 2010 at 2:00 pm
vmrao
Your would most likely get more assistance if you posted your table definition and sample data as described in the first link of my signature block ... and with 20 visits I would think that you would know that ....
May 11, 2010 at 2:07 pm
I got the solution. Hope it helps someone else.
select
CollectionUrl
, MAX(LastAccessedDate)
from dbo.tracking
group by CollectionUrl
having DateDiff(dd, MAX(LastAccessedDate), GETDATE()) > 60
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply