計算回報值 - 1、3、6 個月回報

如何計算第 1 個月、第 3 個月和第 6 個月的回報？

我有這個查詢，但 ValuationDate 不會是 X 個月前。

例如2015-01-30在計算中返回2014-10-30 。但是，表中僅存在2014-10-31。那麼如何在表中返回該月的最新日期？

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

ValuationDate 是 DATETIME。使用 SQL Server 2008R2

所以在下表數據中，我想在 ValuationDate 2015-01-30 的行上返回 2014-10-31 的 PriceValue

表數據：

目前尚不清楚，但如果您想要 3 個月後第一行的值，您可以使用ORDER BYwith TOP 1：

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

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

試試這個

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)

引用自：https://dba.stackexchange.com/questions/149544