Who from my location am I better than?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT 
  RANK() OVER (ORDER BY U2.REPUTATION DESC) AS  'Rank',
  U2.DisplayName AS 'Display Name',
  U2.Reputation AS 'Reputation',
  U2.Location AS 'Location',
  CAST((DATEDIFF(SECOND, U2.CreationDate, GETDATE())/60/60/24/365) AS NVARCHAR) + 
  CASE 
    WHEN DATEDIFF(SECOND, U2.CreationDate, GETDATE())/60/60/24/365 <> 1 THEN ' years '
    ELSE ' year '
  END +
  + CAST((DATEDIFF(SECOND, U2.CreationDate, GETDATE())/60/60/24%365) AS NVARCHAR) + ' days '
  + CAST((DATEDIFF(SECOND, U2.CreationDate, GETDATE())/60/60%24) AS NVARCHAR) + ' hours '
  + CAST((DATEDIFF(SECOND, U2.CreationDate, GETDATE())/60%60) AS NVARCHAR) + ' minutes '
  + CAST((DATEDIFF(SECOND, U2.CreationDate, GETDATE())%60) AS NVARCHAR) + ' seconds ' AS 'Member For'
FROM USERS U1
  JOIN USERS U2 ON U1.LOCATION = U2.LOCATION
WHERE
  1 = 1
  AND U1.DisplayName = 'jeremysprofile'
  AND U1.REPUTATION >= U2.REPUTATION
ORDER BY U2.REPUTATION DESC, U2.DISPLAYNAME

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results