SQL server select distinct rows using values befor

Question

There was a question asked three years ago that was fully answered so I did not want to pollute that as this is an extension to that question. The earlier question was this .

OK, I have a table that has the following columns

• Id
• ForeignKeyId
• AttributeName
• AttributeValue
• Created

Some of the data may look like this:

ID, ForeignKeyId, AttributeName,    AttributeValue, Created
1,    1,         'EmailPreference',   'Text',       1/1/2010
2,    1,         'EmailPreference',   'Html',       1/3/2010
3,    1,         'EmailPreference',   'Text',       1/10/2010
4,    2,         'EmailPreference',   'Text',       1/2/2010
5,    2,         'EmailPreference',   'Html',       1/8/2010

I want to run a query that tells me the latest 'Created' Date for a specified REQUEST_DATE for each distinct ForeignKeyId and AttributeName, using the Created column to determine the most recent value.

Example output for a CreatedDate of '1/4/2010' would be:

ForeignKeyId AttributeName    AttributeValue Created
-------------------------------------------------------
1           'EmailPreference' 'Text'         1/2/2010
2           'EmailPreference' 'Html'         1/3/2010

Example output for a CreatedDate of '1/9/2010' would be:

ForeignKeyId AttributeName    AttributeValue Created
-------------------------------------------------------
1           'EmailPreference' 'Text'         1/2/2010
2           'EmailPreference' 'Html'         1/8/2010

How can I do this with SQL Server 2008?

So far I have:

DECLARE @REQUEST_DATE varchar(10)
SELECT @REQUEST_DATE = '1/9/2010'

select t1.* from (select ForeignKeyId,AttributeName, max(Created) AS MaxCreated
  from  YourTable
group by ForeignKeyId,AttributeName) t2
join YourTable t1 on 
   t2.ForeignKeyId = t1.ForeignKeyId
   and t2.AttributeName = t1.AttributeName
   and t2.MaxCreated = t1.Created

But I can not suss out where to put the restriction to only include rows with a 'Created' before the requested date

Answer 1

DECLARE @REQUEST_DATE varchar(10)
SELECT @REQUEST_DATE = '1/9/2010'

select t1.* from (select ForeignKeyId,AttributeName, max(Created) AS MaxCreated
  from  YourTable
where created < @REQUEST_DATE
group by ForeignKeyId,AttributeName) t2
join YourTable t1 on 
   t2.ForeignKeyId = t1.ForeignKeyId
   and t2.AttributeName = t1.AttributeName
   and t2.MaxCreated = t1.Created