window.sql

WITH RankedMonthlySizes AS
(
	SELECT Matter_MatterId AS MatterId,
	       HostingFilesize,
	       DENSE_RANK() OVER (PARTITION BY Matter_MatterId ORDER BY MonthAndYear DESC) AS DateRank
	FROM dbo.RelativityMatterFilesizes
)
SELECT s.MatterId, s.HostingFilesize
FROM RankedMonthlySizes AS s
JOIN dbo.Matters AS m ON m.MatterId = s.MatterId
WHERE s.DateRank = 1 AND (m.InRelativity = 1 AND (m.IsHibernated != 1 AND m.IsHidden != 1) AND m.RelativityArtifactId IS NOT NULL)