DECLARE @SQLString NVARCHAR(MAX); DECLARE @SQLSubStringQ ...

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

DECLARE @SQLString NVARCHAR(MAX);
DECLARE @SQLSubStringQ NVARCHAR(2000);
DECLARE @SQLSubStringA NVARCHAR(2000);
/* This NUMERIC constant means [...] */
DECLARE @CONSTANT_NUM_1 NUMERIC(19,9) = 1.9208;
/* This NUMERIC constant means [...] */
DECLARE @CONSTANT_NUM_2 NUMERIC(19,9) = 1.96;
/* This NUMERIC constant means [...] */
DECLARE @CONSTANT_NUM_3 NUMERIC(19,9) = 0.9604;
/* This NUMERIC constant means [...] */
DECLARE @CONSTANT_NUM_4 NUMERIC(19,9) = 3.8416;
DECLARE @positive NVARCHAR(100) = 'CAST(SUM(IIF(Votes.VoteTypeId = 2, 1, 0)) AS NUMERIC(19, 9))';
DECLARE @negative NVARCHAR(100) = 'CAST(SUM(IIF(Votes.VoteTypeId = 3, 1, 0)) AS NUMERIC(19, 9))';
SET @SQLSubStringQ = '
  SELECT (
    (' + @positive + ' + ' + '1.9208) / (' + @positive + ' + ' + @negative + ') -
    1.96 * SQRT((' + @positive + ' * ' + @negative + ') / (' + @positive + ' + ' + @negative + ') + 0.9604) / 
    (' + @positive + ' + ' + @negative + ')) / (1 + 3.8416 / (' + @positive + ' + ' + @negative + '))
  FROM Posts
  INNER JOIN Votes
  ON Posts.Id = Votes.PostId
  WHERE Posts.Id = ANY (SELECT a.PostId FROM PostTags a WHERE a.TagId = PostTags.TagId)
  HAVING ' + @positive + ' + ' + @negative + ' > 0';
SET @SQLSubStringA = '
  SELECT (
    (' + @positive + ' + ' + '1.9208) / (' + @positive + ' + ' + @negative + ') -
    1.96 * SQRT((' + @positive + ' * ' + @negative + ') / (' + @positive + ' + ' + @negative + ') + 0.9604) / 
    (' + @positive + ' + ' + @negative + ')) / (1 + 3.8416 / (' + @positive + ' + ' + @negative + '))
    FROM Posts
    INNER JOIN Votes
    ON Posts.Id = Votes.PostId
    WHERE Posts.ParentId = ANY (SELECT a.PostId FROM PostTags a WHERE a.TagId = PostTags.TagId)
    HAVING ' + @positive + ' + ' + @negative + ' > 0';
-- Workaround: https://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated/17785175#17785175
SET @SQLString = CAST('' as NVARCHAR(MAX)) + '
  SELECT 
    Tags.TagName,
    Tags.Count AS QuestionCount,
    SUM(Posts.Score) AS NetQuestionScore,
    (' + @SQLSubStringQ + ') AS QuestionRatings,
    SUM(Posts.AnswerCount) AS AnswerCount,
    (
      SELECT COALESCE(SUM(a.Score), 0) 
      FROM Posts a 
      WHERE a.ParentId = ANY
      (
        SELECT b.PostId 
        FROM PostTags b 
        WHERE b.TagId = PostTags.TagId
      )
    ) AS NetAnswerScore,
    (' + @SQLSubStringA + ') AS AnswerRatings
  FROM Tags
  INNER JOIN PostTags
    ON Tags.Id = PostTags.TagId
  INNER JOIN Posts
    ON PostTags.PostId = Posts.Id
    AND Posts.PostTypeId IN (1, 2, 3)
  WHERE 
    (' + @SQLSubStringQ + ') IS NOT NULL 
    OR (' + @SQLSubStringA + ') IS NOT NULL
  GROUP BY 
    PostTags.TagId, 
    Tags.TagName, 
    Tags.Count
  ORDER BY 
    Tags.TagName ASC';
EXECUTE(@SQLString);
--SELECT @SQLString;

Enter Parameters

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