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