Echo Echo - 2 months ago 14
SQL Question

SQL: Last_Value() returns wrong result (but First_Value() works fine)

I have a table in SQL Server 2012 as the snapshot shows:

enter image description here

Then I'm using Last_Value() and First Value to get AverageAmount of each EmpID for different YearMonth. The script is as follows:

SELECT A.EmpID,
First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS '200901AvgAmount',
Last_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS '201112AvgAmount'

FROM Emp_Amt AS A


However, the result for this query is:

result

In column of "201112AvgAmount", it shows different values for each EmpID while "200901AvgAmount" has correct values.

Is there anything wrong with my SQL script? I did a lot research online but still cannot find the answer....

www www
Answer

There is nothing wrong with your script, this is a way how partitioning works in SQL server :/. If you change LAST_VALUE to MAX result will be the same. Solution would be:

SELECT A.EmpID,  
       First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS  '200901AvgAmount', 
       Last_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS '201112AvgAmount'  
FROM  Emp_Amt  AS A

There is a great post about it, link. GL!

Comments