The First_Value () is introduced in SQL Server 2012. This function will return the first value form the list or in the group.

Example
  1. Create table tableNetAssestValue(Id int identity, Date Datetime, NetAssestValue float)
  2. Insert into tableNetAssestValue values('2017-02-13 00:00:00.000',100)
  3. Insert into tableNetAssestValue values('2017-02-14 00:00:00.000',150)
  4. Insert into tableNetAssestValue values('2017-02-15 00:00:00.000',160)
  5. Insert into tableNetAssestValue values('2017-02-16 00:00:00.000',170)
  6. Insert into tableNetAssestValue values('2017-02-17 00:00:00.000',180)
  7. SELECT
  8. Id,
  9. Date,
  10. NetAssestValue,
  11. FIRST_VALUE(NetAssestValue) OVER (ORDER BY Date) As FirstValue
  12. FROM tableNetAssestValue
Suppose you have the Net Asset value for a couple of days and you need to calculate the Rate of return from that net asset value. The formula given below will be used to calculate the rate of return.
  1. Rate of return = (Current date net assets value - base line value (First_Value))/ base line value (First_Value))
  2. SELECT
  3. Id,
  4. Date,
  5. NetAssestValue,
  6. FIRST_VALUE(NetAssestValue) OVER (ORDER BY Date) As BaseLineValue,
  7. (NetAssestValue - FIRST_VALUE(NetAssestValue) OVER (ORDER BY Date))/FIRST_VALUE(NetAssestValue) OVER (ORDER BY Date)
  8. As RateOfReturn
  9. FROM tableNetAssestValue