SELECT s.SiteId, s.SiteName,
CASE WHEN sc.SiteCategoryName = 'Closed Studies' THEN 'Closed' ELSE 'Active' END [Study Status],
s.CreatedDate [Date eTMF built in TI],
COUNT(doc.TopicId) [Total # of Documents],
ISNULL(SUM(doc.Last2Monthes), 0) [Total # of Documents for last 2 months],
ISNULL(SUM(doc.Last2Weeks), 0) [Total # of Documents for last 2 weeks],
ISNULL(SUM(doc.LastWeek), 0) [Total # of Documents for last week]
FROM [Site] s
INNER JOIN dbo.SiteMetadata sm ON sm.SiteId = s.SiteId AND (sm.[Room Type] = 'Production' OR ISNULL(sm.[Room Type], N'')=N'') AND ISNULL(sm.IncludeinPPGroomslist, 0) = 0
LEFT JOIN dbo.SiteCategory sc ON sc.SiteCategoryId = sm.SiteCategoryId
LEFT JOIN (
    SELECT TopicId, SiteId,
     CASE WHEN SubmittedOn > DATEADD(MONTH, -2, GETDATE()) THEN 1 ELSE 0 END AS Last2Monthes,
     CASE WHEN SubmittedOn > DATEADD(week, -2, GETDATE()) THEN 1 ELSE 0 END AS Last2Weeks,
     CASE WHEN SubmittedOn > DATEADD(week, -1, GETDATE()) THEN 1 ELSE 0 END AS LastWeek
    FROM dbo.DocumentMetadataVirtual
    WHERE Deleted = 0
) doc ON s.SiteId = doc.SiteId
WHERE s.StatusId = 1
GROUP BY s.SiteId, s.SiteName, sc.SiteCategoryName, s.CreatedDate
ORDER BY s.SiteId