/* GMS Active Assignee - summary (DatawarehouseNCM) Fact: dbo.FactGMSActiveAssignee Assignment counts rolled up by client and by status. */ DECLARE @CorpCode varchar(15) = NULL; -- NULL / '' = all DECLARE @Status varchar(30) = NULL; -- NULL / '' = all -- By client and status SELECT f.CorpCode, f.FormalName, f.Status, COUNT(*) AS [AssignmentCount] FROM dbo.FactGMSActiveAssignee f WHERE (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) AND (@Status IS NULL OR @Status = '' OR f.Status = @Status) GROUP BY f.CorpCode, f.FormalName, f.Status ORDER BY f.CorpCode, f.Status; -- By status only SELECT f.Status, COUNT(*) AS [AssignmentCount], COUNT(DISTINCT f.TEFileNo) AS [AssigneeCount], COUNT(DISTINCT f.CorpCode) AS [ClientCount] FROM dbo.FactGMSActiveAssignee f WHERE (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) AND (@Status IS NULL OR @Status = '' OR f.Status = @Status) GROUP BY f.Status ORDER BY f.Status;