DECLARE @MinCount int select @MinCount = ##MinCountOfQu...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Space Exploration

Q&A for spacecraft operators, scientists, engineers, and enthusiasts

--
--
   

DECLARE @MinCount int
select @MinCount = ##MinCountOfQuestionsPerBadge?1##

SELECT id, tagname, count, -999 AS 'creatorid'  -- Can't use -1 as that's community
INTO   #t1 
FROM   tags 
WHERE  count > @MinCount
GO
-- DisplayName
DECLARE @userid int
SELECT  @userid=##UserId##
DECLARE @id int
DECLARE @creatorid int
DECLARE @tagname nvarchar (25)

WHILE (EXISTS (SELECT 1 FROM #t1 WHERE creatorid = -999))
BEGIN
    SELECT @id=min(id) 
    FROM   #t1 
    WHERE  creatorid = -999
    
    SELECT @tagname=tagname 
    FROM   #t1 
    WHERE  id=@id
    
    SELECT @creatorid = NULL
    
    SELECT @creatorid = userid
    FROM   PostHistory PH1
    WHERE  text like '%<'+@tagname+'>%'
     AND   PostHistoryTypeId IN (3,6,9)
     AND   userid = ##UserId##
     AND   NOT EXISTS (SELECT 1 
                       FROM   PostHistory PH2
                       WHERE  PH2.text like '%<'+@tagname+'>%'
                        AND   PH2.PostHistoryTypeId in (3,6,9)
                        AND   PH1.CreationDate > PH2.CreationDate)


    SELECT @creatorid = COALESCE(@creatorid, -1000) -- in case it's null, so we don't get infinite loop
    UPDATE #t1 
    SET    creatorid = @creatorid
    WHERE  id=@id
    
END
go

DECLARE @badge int

SELECT @badge=count(1) from badges
WHERE  Name   = 'Taxonomist'
  AND  userid = ##UserId##
  
IF (@badge > 0)
BEGIN
    INSERT #t1
    VALUES
    (0, '!Already have Taxonomist!', 10000, ##UserId##)
END

SELECT tagname, count
      , CASE WHEN count < 50 THEN 50-count ELSE 0 END AS 'Questions Left Till Taxonomist'
      , id AS 'TagId', creatorid as 'UserId'
FROM   #t1
WHERE  creatorid = ##UserId##
order by count DESC
DROP TABLE #t1
go

Enter Parameters

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