1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

CREATE PROCEDURE #to_url @DB NVARCHAR(MAX), @URL NVARCHAR(MAX) OUT AS BEGIN
  SELECT @URL = CONCAT(value, '.', @URL) FROM STRING_SPLIT(@DB, '.')
  SET @URL = CONCAT(LOWER(@URL), 'com')
  SELECT @URL = CASE @URL
    WHEN 'meta.mathoverflow.stackexchange.com' THEN 'meta.mathoverflow.stackexchange.net'
    WHEN 'audio.stackexchange.com' THEN 'avp.stackexchange.com'
    WHEN 'meta.audio.stackexchange.com' THEN 'meta.avp.stackexchange.com'
    ELSE @URL
  END
  SET @URL = CONCAT('http://', @URL, '/q/')
END;
GO

DECLARE @pscore int
DECLARE @nscore int

SET @pscore = ##PosScoreThreshold?10##
SET @nscore = ##NegScoreThreshold?-10##

DECLARE @db sysname
DECLARE @query nvarchar(max)
DECLARE c CURSOR FOR 
  SELECT Name 
    FROM sys.databases 
   WHERE database_id > 5
     AND Name LIKE '%.Meta'

CREATE TABLE #PS (Site sysname,
                  K uniqueidentifier,
                  Link nvarchar(max),
                  Id int,
                  Score int,
                  CreationDate datetime,
                  ClosedDate datetime,
                  Tags nvarchar(250))

OPEN c
FETCH NEXT FROM c INTO @db
WHILE (@@FETCH_STATUS = 0) BEGIN
  SET @query =
    '
    DECLARE @url nvarchar(max)
    EXEC #to_url ''' + @db + ''', @url OUT;
    INSERT INTO #PS
    SELECT ''' + @db + ''', NEWID(), CONCAT(@url, Id), Id, Score, CreationDate, ClosedDate, Tags
      FROM ' + QUOTENAME(@db) + '.dbo.Posts
     WHERE PostTypeId = 1
       AND CreationDate >= ''##StartDate?2015-01-01 00:00:00##''
       AND CreationDate <= ''##EndDate?2016-01-01 00:00:00##''
       AND ((Tags LIKE ''%<feature-request>%'' AND
             Tags LIKE ''%<status-%'') OR
            (Tags LIKE ''%<discussion>%'' AND
             Tags NOT LIKE ''%<feature-request>%''))    
       AND Tags NOT LIKE ''%<bug>%''
    '
  EXEC(@Query)
  FETCH NEXT FROM c INTO @db
END;
CLOSE c;
DEALLOCATE c; 

-- Check for discussion tag below instead of above solely so we can omit
-- the discussion tag check for negatively scored status-completed FR's, which
-- are really rare. Note: I toyed with requiring/prohibiting discussion in the
-- FR lists, not sure which is best, but ones tagged discussion seem more
-- interesting for this purpose.

SELECT * FROM (SELECT TOP ##SamplesX2?10## 'poc' c, * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       Tags LIKE '%<discussion>%' AND
       Tags LIKE '%<status-completed>%' AND
       Score >= @pscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples?5## 'noc' c, * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       Tags LIKE '%<status-completed>%' AND
       Score < 0 AND -- These are pretty rare so @nscore threshold usually not met.
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## 'pon' c, * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       Tags LIKE '%<discussion>%' AND
       Tags LIKE '%<status-declined>%' AND
       Score >= 1 AND -- There's not a lot with high scores
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## 'non' c, * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       Tags LIKE '%<discussion>%' AND
       Tags LIKE '%<status-declined>%' AND
       Score <= -1 AND -- There's not a lot with high scores
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## 'nc.' c, * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       Tags LIKE '%<discussion>%' AND
       ClosedDate IS NOT NULL AND
       Score <= @nscore 
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##SamplesX2## 'pod' c, * FROM #PS
 WHERE Tags LIKE '%<discussion>%' AND
       Score >= @pscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## 'nod' c, * FROM #PS
 WHERE Tags LIKE '%<discussion>%' AND
       Score <= @nscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## 'ncd' c, * FROM #PS
 WHERE Tags LIKE '%<discussion>%' AND
       ClosedDate IS NOT NULL AND
       Score <= @nscore
 ORDER BY K) x

fork query

edited may 24 17

created may 24 17 Jason C