I am trying to find the minimum difference between years for each ID. For example,
I have this data:
ID year 1 2001 1 2001 2 2003 2 2004 2 2010 3 2000
and I want this output:
ID year 1 0 2 1 3 0
Advertisement
Answer
You could use a LEFT JOIN
SELECT Id, ISNULL(MIN(Def), 0) [Year] FROM ( SELECT T.Id, TT.Year - T.Year Def FROM Data T LEFT JOIN Data TT ON T.Id = TT.Id AND T.Year < TT.Year ) T GROUP BY Id;