1

Please login or register to vote for this query.

(click on this box to dismiss)

It is normalized by that not all months have 31, 30, 29th days.

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

DECLARE @TagId INT;

SELECT @TagId = Id FROM Tags WHERE TagName = ##TagName:string##;

WITH Raw AS (
  SELECT
    DAY(Votes.CreationDate) AS DoM,
    COUNT(*) AS Count
  FROM Posts, Votes, PostTags
  WHERE
    Votes.PostId = Posts.Id
    AND
    Posts.ParentId = PostTags.PostId
    AND
    Votes.VoteTypeId BETWEEN 1 AND 2
    AND
    PostTags.TagId = @TagId
  GROUP BY DAY(Votes.CreationDate)
),
Raw2 AS (
  SELECT
    DoM,
    CASE
      WHEN DoM = 31 THEN 12.0/7
      WHEN DoM = 30 THEN 12.0/11
      WHEN DoM = 29 THEN 12.0/11.25
      ELSE 1
    END * Count AS Count
  FROM Raw
),
Raw3 AS (
  SELECT SUM(Count) AS SumCount FROM Raw2
)
SELECT
  Raw2.DoM,
  CASE WHEN ##Pct:int?1## <> 0 THEN 'Pct' ELSE 'Count' END,
  1.0 * Raw2.Count
    *
    CASE
      WHEN ##Pct## <> 0 THEN 100 / Raw3.SumCount
      ELSE 1
    END AS Value
FROM Raw2, Raw3

UNION ALL

SELECT
  1,
  '',
  0
ORDER BY DoM

fork query

edited mar 9 20

created mar 9 20 peterh (2)