Why is LAST_VALUE() not working in SQL Server?

Question

Here's the data I have (note that this is for only one entity id / employee id, there will be multiple. One entity ID can have multiple employee IDs under it):

SELECT  EntityId,
        EmployeeId,
        PayPeriodStart,
        IsFullTime
FROM    dbo.Payroll
WHERE   EmployeeId = 316691
        AND PayPeriodStart <= '12/31/2014'
        AND PayPeriodEnd >= '1/1/2014';

I want to grab the LAST "IsFullTime" value FOR EACH EntityID & EmployeeID combo.

I tried doing this:

SELECT  EntityId,
        EmployeeId,
        LAST_VALUE(IsFullTime) OVER (PARTITION BY EntityId, EmployeeId ORDER BY EntityId, EmployeeId, PayPeriodStart) AS LastIsFullTimeValue
FROM    dbo.Payroll
WHERE   EmployeeId = 316691
        AND PayPeriodStart <= '12/31/2014'
        AND PayPeriodEnd >= '1/1/2014';

But I'm getting this:

The query should return only ONE row FOR EACH EntityID / EmployeeID.

What am I doing wrong?

Answer 1

Try adding ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

I believe the default window for analytic functions that includes an ORDER BY ends at the current row.

LAST_VALUE(IsFullTime) OVER (
    PARTITION BY EntityId, EmployeeId
    ORDER BY EntityId, EmployeeId, PayPeriodStart
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LastIsFullTimeValue