/* Exception Sample Report — totals / counts (DatawarehouseNCM) Separate from the detail extract. One row per ExceptionStatus: Processed / Approved / Pending / Denied (Crystal @StatusOrder 0–3) */ -- DECLARE @CorpCode VARCHAR(4) = NULL; -- DECLARE @StartDate DATETIME = '2026-06-01'; -- DECLARE @EndDate DATETIME = '2026-06-29'; -- DECLARE @DomesticIntl INT = 1; -- DECLARE @ReloPolicy VARCHAR(100) = NULL; SELECT f.ExceptionStatus, COUNT(*) AS ExceptionCount, SUM(ISNULL(f.EstimatedAmount, 0.00)) AS EstimatedAmountTotal, SUM(ISNULL(f.ProcessedAmount, 0.00)) AS ProcessedAmountTotal, SUM(CASE WHEN f.CostImpactDesc LIKE '%Increase%' THEN ISNULL(f.CostIncrease, 0.00) ELSE 0.00 END) AS CostIncreaseTotal, SUM(CASE WHEN f.CostImpactDesc LIKE '%Savings%' THEN ISNULL(f.SavingsAmount, 0.00) ELSE 0.00 END) AS SavingsAmountTotal FROM dbo.FactExceptionManagement f INNER JOIN dbo.DimMain dm ON dm.DimMainID = f.DimMainID LEFT JOIN dbo.DimPolicy pol ON pol.DimPolicyID = f.DimPolicyID WHERE f.ExceptionStatus IS NOT NULL -- AND (@CorpCode IS NULL OR dm.CorpCode = @CorpCode) -- AND (@StartDate IS NULL OR f.CreatedOn >= @StartDate) -- AND (@EndDate IS NULL OR f.CreatedOn < DATEADD(DAY, 1, @EndDate)) -- AND (@DomesticIntl IS NULL OR f.DomesticIntl = @DomesticIntl) -- AND (@ReloPolicy IS NULL OR @ReloPolicy = '' OR pol.ReloPolicy = @ReloPolicy) GROUP BY f.ExceptionStatus ORDER BY CASE f.ExceptionStatus WHEN 'Processed' THEN 0 WHEN 'Approved' THEN 1 WHEN 'Pending' THEN 2 WHEN 'Denied' THEN 3 ELSE 4 END;