Why is LAST_VALUE () not working in SQL Server?
Here is the data I have (note that this is only for one id / employee id of the object, there will be multiple. One id can have multiple employee id 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 get the LAST value "IsFullTime" for each EntityID and EmployeeID.
I tried to do 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 get this:
The query should only return ONE Row FOR EACH EntityID / EmployeeID.
What am I doing wrong?
source to share
I believe you want to use ROW_NUMBER()
and get the latest value based on payperiodstart
date:
SELECT t.EntityId
,t.EmployeeId
,t.LastIsFullTimeValue
FROM (
SELECT EntityId
,EmployeeId
,ROW_NUMBER() OVER (
PARTITION BY EntityId
,EmployeeId ORDER BY PayPeriodStart DESC
) AS rn
,LastIsFullTimeValue
FROM dbo.Payroll
WHERE EmployeeId = 316691 -- you could probably put this in your outer query instead
AND PayPeriodStart <= '12/31/2014'
AND PayPeriodEnd >= '1/1/2014'
) t
WHERE t.rn = 1;
source to share
Try to add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
I assume this is the default window for analytic functions, including the end ORDER BY
on the current line.
LAST_VALUE(IsFullTime) OVER (
PARTITION BY EntityId, EmployeeId
ORDER BY EntityId, EmployeeId, PayPeriodStart
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LastIsFullTimeValue
source to share
I am using FIRST_VALUE
with DESC
ordering instead . Works.
YourLastValue = FIRST_VALUE(IsFullTime) OVER (
PARTITION BY EntityId, EmployeeId
ORDER BY EntityId DESC, EmployeeId DESC, PayPeriodStart DESC
) AS LastIsFullTimeValue
Agreed, this confuses why it LAST_VALUE
doesn't work as expected.
source to share