First & Last Value

/* LAST_VALUE() function returns the last value among the set of ordered values according to specified ordered & partitioned criteria.
One difference between firstvalue and lastvalue is, for a group of rows, FIRST_VALUE() will return same value,
where as LAST_VALUE() function will return the last value until that row in that group.
*/

USE [ExploringSQL]
GO

DECLARE @Salaries TABLE
(
DepartmentID INT,
Salary INT  
)

INSERT INTO @Salaries (DepartmentID,Salary)
VALUES (1,23),(1,25),(1,21),(2,35),(2,15)

SELECT * FROM @Salaries

-- FIRST VALUE

SELECT DepartmentID,Salary,
FIRST_VALUE(Salary) OVER (PARTITION BY DepartmentID
ORDER BY Salary) AS FirstSalaryInDepartment
FROM @Salaries

-- LAST VALUE

SELECT DepartmentID,Salary,
LAST_VALUE(Salary) OVER (PARTITION BY DepartmentID
ORDER BY Salary) AS LastSalaryInDepartment
FROM @Salaries

USE AdventureWorks2012;
GO

SELECT Department, LastName, Rate, HireDate,
    LAST_VALUE(HireDate) OVER (PARTITION BY Department ORDER BY Rate) AS LastValue
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS eph 
    ON eph.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department IN (N'Information Services',N'Document Control');


SELECT BusinessEntityID, DATEPART(QUARTER,QuotaDate)AS Quarter, YEAR(QuotaDate) AS SalesYear,
    SalesQuota AS QuotaThisQuarter,
    SalesQuota - FIRST_VALUE(SalesQuota)
        OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate)
              ORDER BY DATEPART(QUARTER,QuotaDate) ) AS DifferenceFromFirstQuarter,
    SalesQuota - LAST_VALUE(SalesQuota)
        OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate)
              ORDER BY DATEPART(QUARTER,QuotaDate)
              RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
               ) AS DifferenceFromLastQuarter
FROM Sales.SalesPersonQuotaHistory
WHERE YEAR(QuotaDate) > 2005
AND BusinessEntityID BETWEEN 274 AND 275
ORDER BY BusinessEntityID, SalesYear, Quarter;