Sql-server – Calculate Return Value – 1,3,6 Month Returns

datesql-server-2008-r2t-sql

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:

Best Answer

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 ;

Related Solutions

Sql-server – How to find XXth day of previous month in SQL server

DECLARE @d DATETIME = '20140311', @dm TINYINT = 26;

SELECT DATEADD(DAY, @dm-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d)));

Result:

2014-02-26 00:00:00.000

Sql-server – How to return data from a table where the data is the max date of the previous month’s data submissions (-1 of current date)

Expanding on James' answer

Try this code on SqlFiddle.

Also check out @SqlKiwi Paul White's post regarding Delete vs Truncate

create table tracker_live (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live primary key (client_nbr, tracker_date, submit_date));

insert into tracker_live (client_nbr, tracker_date, submit_date, live, retainer, bonus) values
   (9,'2014-10-01','2014-10-08',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-08',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-08',45585.4029,3,3)
  ,(9,'2014-10-01','2014-10-17',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-17',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-17',45585.4029,3,3)
  ,(9,'2014-10-01','2014-11-13',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-13',907925.1072,2,2)
  ,(9,'2014-12-01','2014-11-13',392917.5237,3,3)
  ,(9,'2014-10-01','2014-11-18',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-18',795725.1072,2,2)
  ,(9,'2014-12-01','2014-11-18',725835.0474,3,3)

create table tracker_live_last_month (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live_last_month primary key (client_nbr, tracker_date));  

/* example query for procedure */


declare @test date;
  set @test = '2014-11-18';
  --set @test = convert(date,getdate());
declare @LastDayOfLastMonth date;
  set @LastDayOfLastMonth  = dateadd(day,-datepart(day,@test),@test);
declare @FirstDayOfLastMonth date;  
  set @FirstDayOfLastMonth = dateadd(day,1-datepart(day,@LastDayOfLastMonth),@LastDayOfLastMonth);

  truncate table tracker_live_last_month;

  /* inserting the data with this combined query should be quicker than creating the table and then updating values */
  insert into tracker_live_last_month (client_nbr, tracker_date, submit_date, live, retainer, bonus)
    select tl.client_nbr, tl.tracker_date, ca.submit_date, ca.live, ca.retainer, ca.bonus
      from tracker_live tl
        cross apply (select top 1 i.submit_date, i.live, i.retainer, i.bonus 
          from tracker_live i 
          where i.client_nbr=tl.client_nbr 
            and i.tracker_date=tl.tracker_date
          order by i.submit_date desc) as ca
      where tl.submit_date between @FirstDayOfLastMonth and @LastDayofLastMonth
      group by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus
      order by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus;

Best Answer

Related Solutions

Sql-server – How to find XXth day of previous month in SQL server

Sql-server – How to return data from a table where the data is the max date of the previous month’s data submissions (-1 of current date)

Related Question