Posts with tag by day

0

Please login or register to vote for this query.

(click on this box to dismiss)

Spanish Language

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

DECLARE @Days TABLE
(
  Day datetime, 
  PostsCount int
)

INSERT INTO @Days (Day, PostsCount)
  SELECT
        CONVERT(DATETIME, CONVERT(VARCHAR(16), Posts.CreationDate, 106)) 'Day',
        COUNT(Posts.id) PostsCount
    FROM Tags
        INNER JOIN PostTags ON PostTags.TagId = Tags.id
        INNER JOIN Posts ON Posts.id = PostTags.PostId
    WHERE 
        Tags.TagName = '##TagName##'
        and posts.creationdate > '2016-04-01'
    GROUP BY
        CONVERT(DATETIME, CONVERT(VARCHAR(16), Posts.CreationDate, 106))

SELECT 
    Days.Day,
    Days.PostsCount
    --SUM(Days2.PostsCount) Total
FROM
    @Days Days
INNER JOIN
    @Days Days2 ON Days.Day >= Days2.Day
GROUP BY
    Days.Day, Days.PostsCount
ORDER BY
    Days.Day

Enter Parameters

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