1

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

Community
  • 1
  • 1
Bill Comer
  • 1,033
  • 1
  • 14
  • 24
  • 1
    You have not shown the `REQUEST_DATE` column, neither in your query nor in your sample data. – Tim Schmelter Nov 06 '13 at 08:49
  • How do you get `1/3/2010` when for `ForeignKeyId` CreatedDate of '1/4/2010' in the first result set? – Kaf Nov 06 '13 at 08:54
  • Tim, the REQUEST_DATE is not a column. It is the restriction I want to put on the CREATED date. – Bill Comer Nov 06 '13 at 09:02
  • Kaf, The Data has two 'Html' rows, '1/3/2012' & '1/8/2010'. As '1/8/2010' is after '1/4/2010' it should be ignored and the '1/3/2010' selected. – Bill Comer Nov 06 '13 at 09:05

1 Answers1

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
Raj
  • 10,653
  • 2
  • 45
  • 52