sql - difference in days, between two recordings -

i have table

user | days    | 1    | 1       | 2 b    | 2 b    | 5 

and i'd have

user | difference_in_day    | 0    | 1    b    | 3 

i think have self-join, don't know how compare row next 1 only.

do need add row position in first table condition ? :

where  a.row_position+1=b.row_position  

please try:

;with t as(     select *, row_number() on (order user, days) rnum yourtable ) select      distinct a.user,      b.days-a.days difference_in_day  t left join t b on a.rnum=b.rnum-1  b.user not null 

sample

declare @tbl table(xuser nvarchar(1), xdays int) insert @tbl values  ('a', 1), ('a', 1), ('a', 2), ('b', 2), ('b', 5)  select *, row_number() on (order xuser, xdays) rnum @tbl  ;with t as(     select *, row_number() on (order xuser, xdays) rnum @tbl ) select      distinct a.xuser,      b.xdays-a.xdays difference_in_day  t left join t b on a.rnum=b.rnum-1  b.xuser not null