0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in conceptual questions about life and challenges in a world where "cognitive" functions can be mimicked in purely digital environment

-- Variables for case-insensitive tag search and date filtering
DECLARE @TagName NVARCHAR(25) = 'jax';
DECLARE @StartDate DATE = '2018-12-01';

-- Main query to fetch post details
SELECT
    p.Id,
    p.CreationDate AS [timestamp],
    p.AnswerCount AS [answer_count],
    p.Body AS [body],
    CAST(CASE WHEN p.AcceptedAnswerId > 0 THEN 1 ELSE 0 END AS BIT) AS [is_accepted],
    CAST(CASE WHEN p.AnswerCount > 0 THEN 1 ELSE 0 END AS BIT) AS [is_answered],
    p.LastActivityDate AS [last_activity_date],
    CASE WHEN p.PostTypeId = 2 THEN p.ParentId ELSE NULL END AS [parent_id],
    p.Score AS [score],
    t.TagName AS [tag],
    p.Title AS [title],
    CASE WHEN p.PostTypeId = 1 THEN 'Question' WHEN p.PostTypeId = 2 THEN 'Answer' ELSE 'Other' END AS [type],
    p.OwnerUserId AS [user],
    p.ViewCount AS [views],
    p.ClosedDate AS [closed_date]
FROM
    Posts p
INNER JOIN
    PostTags pt ON p.Id = pt.PostId
INNER JOIN
    Tags t ON pt.TagId = t.Id
WHERE
    LOWER(t.TagName) = LOWER(@TagName)
    AND p.CreationDate >= @StartDate
ORDER BY
    p.CreationDate DESC;

fork query

edited feb 22

created feb 22 anonymous