Late downvotes

0

Please login or register to vote for this query.

(click on this box to dismiss)

Japanese Language

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Japanese language

-- Create cursor for list of sites
DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
DECLARE @sitedbname AS nvarchar(max)
DECLARE @ispersitemeta AS bit

DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
  SiteDbName nvarchar(max),
  Total float,
  LateBy1 float,
  LateBy14 float,
  LateBy60 float
)

-- Step through cursor
OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  
  SET @ispersitemeta = (CASE WHEN @sitedbname LIKE '%Meta%' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END)
  
  IF @ispersitemeta = 0 
  BEGIN
    SET @query = '
      USE [' + @sitedbname + ']

      INSERT INTO #out
      SELECT
        ''' + @sitedbname + ''' AS [SiteDbName],
        SUM(1) AS "Total",
        SUM((CASE WHEN DATEDIFF(day, p.CreationDate, v.CreationDate) >= 1 THEN 1 ELSE 0 END)) AS "LateBy1",
        SUM((CASE WHEN DATEDIFF(day, p.CreationDate, v.CreationDate) >= 14 THEN 1 ELSE 0 END)) AS "LateBy14",
        SUM((CASE WHEN DATEDIFF(day, p.CreationDate, v.CreationDate) >= 60 THEN 1 ELSE 0 END)) AS "LateBy60"
      FROM
        Votes AS v
        INNER JOIN Posts AS p
          ON v.PostId = p.Id
      WHERE
        v.VoteTypeId = 3
      '
    EXEC sp_executesql @query
  END
  
  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites;


SELECT
  *,
  LateBy1/Total AS "LateBy1Ratio",
  LateBy14/Total AS "LateBy14Ratio",
  LateBy60/Total AS "LateBy60Ratio"
FROM
  #out

Enter Parameters

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