/* Authorization Report — totals (DatawarehouseNCM) BackOffice: frmClientSrvcSummary (lblRptType = "Auths") Crystal: ClientAuthorizations.rpt Same TE selection as 01 Authorization Report Detail. One row per client: transferee count and non-cancelled client service count. Profit center is omitted. */ DECLARE @CorpCode varchar(12) = NULL; -- NULL or '' = all clients DECLARE @ActiveOnly bit = 0; DECLARE @StartDate date = '2025-01-01'; DECLARE @EndDate date = '2025-12-31'; DECLARE @ParentRollup bit = 0; ;WITH QualifyingTE AS ( SELECT s.TEFileNo FROM dbo.DimTEFullServiceList s WHERE @ActiveOnly = 1 AND s.DisplayType = 'C' AND ISNULL(s.ComponentService, 0) = 0 AND s.MoveComplete IS NULL AND ISNULL(s.REMService, 0) = 0 AND s.CompleteDate IS NULL AND s.CancelDate IS NULL AND ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND s.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND s.corpcode = @CorpCode) ) GROUP BY s.TEFileNo UNION ALL SELECT m.TEFileNo FROM dbo.DimMain m WHERE @ActiveOnly = 0 AND m.AuthDate IS NOT NULL AND CAST(m.AuthDate AS date) >= @StartDate AND CAST(m.AuthDate AS date) <= @EndDate AND ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND m.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND m.CorpCode = @CorpCode) ) ) SELECT m.CorpCode, m.FormalName, m.ParentCompany, COUNT(DISTINCT te.TEFileNo) AS TransfereeCount, COUNT(fs.DimTEFullServiceListID) AS ServiceCount FROM QualifyingTE te INNER JOIN dbo.DimMain m ON m.TEFileNo = te.TEFileNo LEFT JOIN dbo.DimTEFullServiceList fs ON fs.TEFileNo = te.TEFileNo AND fs.DisplayType = 'C' AND fs.CancelDate IS NULL GROUP BY m.CorpCode, m.FormalName, m.ParentCompany ORDER BY m.FormalName, m.CorpCode;