sql server - Unexpected results with LAST_VALUE, CURRENT ROW, and NULLs -

sql server - Unexpected results with LAST_VALUE, CURRENT ROW, and NULLs -

i have next data:

create table #transfers ( addedon datetime2 not null, empid int not null, newdeptid int null ) insert #transfers values ('2013-12-17 17:18:54.3499987', 19, 36), ('2013-12-18 13:02:34.1168087', 19, null), ('2014-01-28 11:41:55.8755928', 22, 100), ('2014-02-05 10:36:36.3645703', 22, null), ('2014-02-16 00:00:00.0000000', 22, 37), ('2014-02-17 00:00:00.0000000', 22, null)

for each row, trying recent non-null newdeptid (until row):

select *, last_value(newdeptid) on ( partition empid order iif(newdeptid null,0,1), addedon rows between unbounded preceding , current row ) currentdeptid #transfers order empid, addedon

if understand correctly, null values should excluded because first values in window -- iif(newdeptid null,0,1).

i expect following:

addedon empid newdeptid currentdeptid 2013-12-17 17:18:54.3499987 19 36 36 2013-12-18 13:02:34.1168087 19 null 36 2014-01-28 11:41:55.8755928 22 100 100 2014-02-05 10:36:36.3645703 22 null 100 2014-02-16 00:00:00.0000000 22 37 37 2014-02-17 00:00:00.0000000 22 null 37

instead, order clause within last_value ignored, , null returned when current row contains null:

addedon empid newdeptid currentdeptid 2013-12-17 17:18:54.3499987 19 36 36 2013-12-18 13:02:34.1168087 19 null null -- 2014-01-28 11:41:55.8755928 22 100 100 2014-02-05 10:36:36.3645703 22 null null -- 2014-02-16 00:00:00.0000000 22 37 37 2014-02-17 00:00:00.0000000 22 null null --

i getting same results in sql server 2012 , 2014.

is bug in sql server, or missing in window function syntax?

note: if expand window include entire partition, nulls ignored:

select *, last_value(newdeptid) on ( partition empid order iif(newdeptid null,0,1), addedon rows between unbounded preceding , unbounded next ) currentdeptid #transfers order empid, addedon

results:

addedon empid newdeptid currentdeptid 2013-12-17 17:18:54.3499987 19 36 36 2013-12-18 13:02:34.1168087 19 null 36 2014-01-28 11:41:55.8755928 22 100 37 2014-02-05 10:36:36.3645703 22 null 37 2014-02-16 00:00:00.0000000 22 37 37 2014-02-17 00:00:00.0000000 22 null 37

no, have not understood well, how window functions work. rows between unbounded preceding , current row applied after order iif..., first rows ordered (first ones having null, rest) , rows ... restriction applied. so, never going work problem.

in detail, over clause creates these "windows", according partition , order by. so, illustration row addedon = '2014-02-17 00:00:00.0000000', rows between unbounded preceding , current row row above , (the 2 rows marked <------):

addedon empid iif() newdeptid -- partition empid = 19 2013-12-18 13:02:34.1168087 19 0 null 2013-12-17 17:18:54.3499987 19 1 36 -- partition empid = 22 2014-02-05 10:36:36.3645703 22 0 null <--- 2014-02-17 00:00:00.0000000 22 0 null <--- last_value 2014-01-28 11:41:55.8755928 22 1 100 2014-02-16 00:00:00.0000000 22 1 37

so, currentdeptid column gets these values:

addedon empid iif() newdeptid currentdeptid -- partition empid = 19 2013-12-18 13:02:34.1168087 19 0 null null 2013-12-17 17:18:54.3499987 19 1 36 36 -- partition empid = 22 2014-02-05 10:36:36.3645703 22 0 null null 2014-02-17 00:00:00.0000000 22 0 null null 2014-01-28 11:41:55.8755928 22 1 100 100 2014-02-16 00:00:00.0000000 22 1 37 37

which re-ordered final result, according external order by

to work around problem, utilize correlated subquery:

select *, ( select top (1) newdeptid #transfers ti ti.empid = t.empid , ti.newdeptid not null , ti.addedon <= t.addedon order addedon desc ) currentdeptid #transfers t order empid, addedon ;

test @ sql-fiddle

what trying create sense if nulls ignored last_value() function. can done ignore nulls unfortuantely feature has not been yet implemented in sql-server. can see how work, in oracle (fiddle-2):

select addedon, empid, newdeptid, last_value(newdeptid) ignore nulls -- check on ( partition empid order addedon rows between unbounded preceding , current row ) currentdeptid transfers order empid, addedon ;

another way, window functions, works in sql-server, counting not-null newdeptid before current row first. can test @ fiddle-3:

with cte ( select addedon, empid, newdeptid, count(newdeptid) on ( partition empid order addedon rows between unbounded preceding , current row ) cnt transfers ) select addedon, empid, newdeptid, min(newdeptid) on (partition empid, cnt) currentdeptid cte order empid, addedon ;

sql-server sql-server-2012 window-functions sql-server-2014