Calculate Return Value - 1,3,6 Month Returns How do I calculate the return for 1mth, 3mths, and 6mths?

I have this query but ValuationDate will not be exactly X months ago.

e.g. 2015-01-30 returns 2014-10-30 in the calculation. However, only 2014-10-31 exists in the table. So how do I return the latest date in the table for that month?

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3,ValuationDate)


ValuationDate is DATETIME. Using SQL Server 2008R2

So in the below table data, I want to return the PriceValue for 2014-10-31 on the row with ValuationDate 2015-01-30

Table Data:
I don't think your code will return any rows as the WHERE clause will never return true.

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3,ValuationDate)


This would only make sense if the ValuationDate value inside the DATEADD function was a literal value or a variable like below:

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3, '20160930')


or

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3, @MyValuationDate)


I'm also assuming that ValuationDate is a DATE type column and not a DATETIME or DATETIME2.

But I think I understand your question and the code below should be what you need:

SELECT PriceValue 
FROM @Output
WHERE ValuationDate = EOMONTH(DATEADD(MONTH, -3, @MyValuationDate))