/* Exception Sample Report — totals at expense ITEM grain (Crystal-style) Counts rows after expanding to FactRelocationExpense (same as Crystal vwREMExceptions). Use 02 for EA-grain totals; use this to compare to Crystal's inflated Processed count. */ -- DECLARE @CorpCode VARCHAR(4) = NULL; -- DECLARE @StartDate DATETIME = '2025-07-15'; -- DECLARE @EndDate DATETIME = '2025-12-15'; -- DECLARE @DomesticIntl INT = 1; -- DECLARE @ReloPolicy VARCHAR(100) = NULL; ;WITH ItemRows AS ( SELECT f.EstimatedAmount, ed.Amount AS ProcessedAmount, CASE WHEN f.CostImpactDesc LIKE '%Increase%' THEN ISNULL(ed.Amount, 0.00) ELSE 0.00 END AS CostIncrease, CASE WHEN f.CostImpactDesc LIKE '%Savings%' THEN ISNULL(f.SavingsAmount, 0.00) ELSE 0.00 END AS SavingsAmount, CASE WHEN f.WorkflowStateID = 38 THEN CASE WHEN f.ItemDenied = 1 THEN 'Denied' WHEN (f.TrackInERP = 1 AND ed.Amount IS NOT NULL AND ed.Amount <> 0) OR f.TrackInERP = 0 THEN 'Processed' ELSE 'Approved' END WHEN f.WorkflowStateID IN (35, 48) THEN 'Denied' ELSE 'Pending' END AS ExceptionStatus FROM dbo.FactExceptionManagement f INNER JOIN dbo.DimMain dm ON dm.DimMainID = f.DimMainID LEFT JOIN dbo.DimPolicy pol ON pol.DimPolicyID = f.DimPolicyID LEFT JOIN dbo.FactRelocationExpense ed ON ed.TEExceptionID = f.TEExceptionID AND f.TEExceptionID <> 0 AND ed.ExceptionLine = 1 AND ed.DeniedExpense = 0 WHERE 1 = 1 -- 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) ) SELECT ExceptionStatus, COUNT(*) AS ExceptionCount, SUM(ISNULL(EstimatedAmount, 0.00)) AS EstimatedAmountTotal, SUM(ISNULL(ProcessedAmount, 0.00)) AS ProcessedAmountTotal, SUM(CostIncrease) AS CostIncreaseTotal, SUM(SavingsAmount) AS SavingsAmountTotal FROM ItemRows GROUP BY ExceptionStatus ORDER BY CASE ExceptionStatus WHEN 'Processed' THEN 0 WHEN 'Approved' THEN 1 WHEN 'Pending' THEN 2 WHEN 'Denied' THEN 3 ELSE 4 END;