FirstValue In SQL Server 2012

In this blog, you will learn about FirstValue in SQL Server 2012.

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

Example

Create table tableNetAssestValue(Id int identity, Date Datetime, NetAssestValue float)
Insert into tableNetAssestValue values('2017-02-13 00:00:00.000',100)
Insert into tableNetAssestValue values('2017-02-14 00:00:00.000',150)
Insert into tableNetAssestValue values('2017-02-15 00:00:00.000',160)
Insert into tableNetAssestValue values('2017-02-16 00:00:00.000',170)
Insert into tableNetAssestValue values('2017-02-17 00:00:00.000',180)
SELECT
Id,
Date,
NetAssestValue,
FIRST_VALUE(NetAssestValue) OVER (ORDER BY Date) As FirstValue
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.

Rate of return = (Current date net assets value - base line value (First_Value))/ base line value (First_Value))
SELECT
Id,
Date,
NetAssestValue,
FIRST_VALUE(NetAssestValue) OVER (ORDER BY Date) As BaseLineValue,
(NetAssestValue - FIRST_VALUE(NetAssestValue) OVER (ORDER BY Date))/FIRST_VALUE(NetAssestValue) OVER (ORDER BY Date)
As RateOfReturn
FROM tableNetAssestValue