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:

enter image description here

  • 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);
AS (
            ORDER BY valuationdate
            ) AS Row
    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')


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
  • 2
    OK I think we need some more details on the question – James Anderson Sep 13 '16 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.