0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientific skepticism

-- Define parameters for date range and tag filtering
DECLARE @StartDate DATETIME = '2018-01-01'; -- Specify the start date for filtering
DECLARE @EndDate DATETIME = '2022-01-01';   -- Specify the end date for filtering
DECLARE @TargetTagName NVARCHAR(255) = 'java '; -- Specify the tag name for filtering
DECLARE @RequestedRowCount INT = 1000;  -- Number of rows to retrieve, adjust as needed

-- Create a temporary table to store query results
CREATE TABLE #TemporaryResults (
    PostId INT,          -- Unique identifier for each post
    PostTitle NVARCHAR(MAX),  -- Title of the post
    PostContent NVARCHAR(MAX),  -- Content of the post
    Tags NVARCHAR(MAX)  -- Tags associated with the post
);

-- Initialize a variable to keep track of the current page
DECLARE @CurrentPageNumber INT = 1;

-- Loop until the desired number of rows are retrieved
WHILE (SELECT COUNT(*) FROM #TemporaryResults) < @RequestedRowCount
BEGIN
    -- Insert relevant data into the temporary table
    INSERT INTO #TemporaryResults (PostId, PostTitle, PostContent, Tags)
    SELECT TOP (@RequestedRowCount - (SELECT COUNT(*) FROM #TemporaryResults))
        Id AS PostId,
        Title AS PostTitle,
        Body AS PostContent,
        Tags
    FROM
        Posts
    WHERE
        PostTypeId = 1
        AND CreationDate BETWEEN @StartDate AND @EndDate
        AND Tags LIKE '%<java>%' -- Only Java questions
        AND Id NOT IN (SELECT PostId FROM #TemporaryResults)
    ORDER BY CreationDate DESC;

    -- Move to the next page
    SET @CurrentPageNumber = @CurrentPageNumber + 1;
END

-- Select the desired columns from the temporary table
SELECT
    PostId,
    PostTitle,
    PostContent,
    Tags
FROM
    #TemporaryResults;

-- Drop the temporary table to free up resources
DROP TABLE #TemporaryResults;

fork query

edited dec 1

created dec 1 anonymous