Q&A for SharePoint enthusiasts
-- Create a temporary table to hold filtered posts CREATE TABLE #FilteredPosts ( OwnerUserId INT, CreationDate DATETIME, PostTypeId INT, Year INT, CustomWeek INT -- Renamed to CustomWeek for clarity ); -- Insert filtered data into the temporary table INSERT INTO #FilteredPosts SELECT OwnerUserId, CreationDate, PostTypeId, YEAR(CreationDate) AS Year, DATEDIFF(WEEK, '2010-01-01', CreationDate) + 1 AS CustomWeek -- Calculate custom week number FROM Posts WHERE OwnerUserId IN ( SELECT Id FROM Users WHERE YEAR(CreationDate) = 2010 -- Specify year only ) AND CreationDate <= (SELECT DATEADD(DAY, 31, MIN(CreationDate)) FROM Posts); -- Now perform the aggregation from the temporary table SELECT Year, CustomWeek, -- Use the CustomWeek for aggregation SUM(CASE WHEN PostTypeId = 1 THEN 1 ELSE 0 END) AS TotalQuestionsCount, SUM(CASE WHEN PostTypeId = 2 THEN 1 ELSE 0 END) AS TotalAnswersCount FROM #FilteredPosts GROUP BY Year, CustomWeek ORDER BY Year, CustomWeek; -- Drop the temporary table DROP TABLE #FilteredPosts;