/* Open Receivables Aging — client summary (DatawarehouseNCM) Crystal: OpenReceivablesAgingClientSummary.rpt */ DECLARE @CorpCode varchar(15) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @ParentCompany varchar(15) = NULL; SELECT CASE WHEN @ParentRollup = 1 THEN f.ParentCompany ELSE f.CorpCode END AS ClientKey, MAX(CASE WHEN @ParentRollup = 1 THEN f.ParentCompany ELSE c.FormalName END) AS ClientName, MAX(f.ParentCompany) AS ParentCompany, COUNT(*) AS OpenInvoiceCount, SUM(f.OpenAmount) AS OpenAmount, SUM(f.CurrentAmt) AS CurrentAmt, SUM(f.Days1To30Amt) AS Days1To30Amt, SUM(f.Days31To60Amt) AS Days31To60Amt, SUM(f.Days61To90Amt) AS Days61To90Amt, SUM(f.DaysOver90Amt) AS DaysOver90Amt, MAX(f.AsOfDate) AS AsOfDate FROM dbo.FactOpenReceivable f LEFT JOIN dbo.DimCorpClient c ON f.CorpCode = c.CorpCode WHERE (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode OR (@ParentRollup = 1 AND f.ParentCompany = @CorpCode)) AND (@ParentRollup = 0 OR @ParentCompany IS NULL OR @ParentCompany = '' OR f.ParentCompany = @ParentCompany) GROUP BY CASE WHEN @ParentRollup = 1 THEN f.ParentCompany ELSE f.CorpCode END ORDER BY ClientKey;