Now addresses the fact that some badges aren't even introduced until a certain date, so some members couldn't have gotten it any sooner since they aren't applied retroactively. Added 1 plus DateDiff (OBOE)
Q&A for scientists using computers to solve scientific problems
-- Quickest badge earners V2b -- Now addresses the fact that some badges aren't even introduced until a certain date, so some members couldn't have gotten it any sooner since they aren't applied retroactively. -- Added 1 plus DateDiff (OBOE) DECLARE @firstTime date SELECT @firstTime = min(Badges.Date) FROM Badges WHERE Badges.Name = '##BadgeName##' ; WITH BadgeEarners AS ( SELECT Users.Id as [User Link], Users.CreationDate as [Member Since], rank() over(partition by Users.Id order by Badges.Date) as [TypeRank], Badges.Date as [Date Won], 1+DateDiff(Day, Users.CreationDate, Badges.Date) As [DaysMembership] FROM Badges INNER JOIN Users ON Badges.UserId = Users.Id WHERE Badges.Name = '##BadgeName##' ) SELECT case [TypeRank] when 1 then 'Bronze' when 2 then 'Silver' when 3 then 'Gold' end as [Type], [User Link], [Member Since], [Date Won], [DaysMembership], DateDiff(Day, @firstTime, [Date Won]) As [DaysSince1st] FROM BadgeEarners ORDER BY [TypeRank] DESC, [DaysMembership] ASC