# 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:

• Is it guaranteed that only one row per month exists? – Oliver Rahner Sep 14 '16 at 6:09
• It is possible that there could be multiple rows per month. So would want the latest date in each month. – K09 Sep 14 '16 at 8:17
• Is it guaranteed that that 'at least' one row is available for each month? – Scott Hodgin Sep 14 '16 at 11:37

It's still not clear but if you want the value from the first row after the 3-month mark, you can use `ORDER BY` with `TOP 1`:

``````DECLARE @MyValuationDate DATE ;
SET @MyValuationDate = '20150130' ;

SELECT TOP (1) PriceValue
FROM @Output
WHERE  ValuationDate >= DATEADD(MONTH, -3, @MyValuationDate)
ORDER BY ValuationDate ;
``````

Give this a try

``````declare @Output table (ValuationDate date, PriceValue decimal (11,2));
insert into @Output (ValuationDate,PriceValue) values('2014-04-30',100.00);
insert into @Output (ValuationDate,PriceValue) values('2014-05-30',200.00);
insert into @Output (ValuationDate,PriceValue) values('2014-06-30',300.00);
insert into @Output (ValuationDate,PriceValue) values('2014-07-31',400.00);
insert into @Output (ValuationDate,PriceValue) values('2014-08-29',500.00);
insert into @Output (ValuationDate,PriceValue) values('2014-09-30',600.00);
insert into @Output (ValuationDate,PriceValue) values('2014-10-31',700.00);
insert into @Output (ValuationDate,PriceValue) values('2014-11-28',800.00);
insert into @Output (ValuationDate,PriceValue) values('2014-12-31',900.00);
insert into @Output (ValuationDate,PriceValue) values('2015-01-30',1000.00);
WITH Data
AS (
SELECT ROW_NUMBER() OVER (
ORDER BY valuationdate
) AS Row
,ValuationDate
,PriceValue
FROM @Output
)
SELECT curr.ValuationDate AS CurrValuationDate
,curr.PriceValue AS CurrPriceline
,minus1.PriceValue AS minus1PriceValue
,minus3.PriceValue AS minus3PriceValue
,minus6.PriceValue AS minus6PriceValue
FROM data curr
LEFT JOIN data minus1 ON minus1.Row = (curr.Row - 1)
LEFT JOIN data minus3 ON minus3.Row = (curr.Row - 3)
LEFT JOIN data minus6 ON minus6.Row = (curr.Row - 6)
``````
• Won't work based on the latest addition to requirements... Could be multiple rows per month (and probably even months with no row?) – Oliver Rahner Sep 14 '16 at 9:15
• @OliverRahner - yep, I guess I assumed the example data posted was what we were shooting for - I should have asked the question you asked :( I have another solution in the works that uses date logic, but I abandoned it when I noticed the pattern of 1 row for each month and thought ROW_NUMBER would fit nicely. Back to the drawing board, unless someone else gets there first, – Scott Hodgin Sep 14 '16 at 9:37

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))
``````
• Hi, it may not necessarily be the last day of the month. So EOMONTH won't work. Thanks – K09 Sep 13 '16 at 15:47
• OK I think we need some more details on the question – James Anderson Sep 13 '16 at 15:50