/* Relocation Expense — fact sample / sanity (DatawarehouseNCM) Fact: dbo.FactRelocationExpense */ -- Recent sample rows SELECT TOP 50 f.FactRelocationExpenseID, f.CorpCode, f.TEFileNo, f.TELastName, f.TEFirstName, f.TransactionDate, f.SentToPayroll, f.Category, f.Item, f.Amount, f.PaidTo, f.DeniedExpense, f.TrackOnly, f.ExceptionLine, f.CategoryExcludeFromStdReports, f.CategoryHomeSaleCosts FROM dbo.FactRelocationExpense f ORDER BY f.TransactionDate DESC, f.FactRelocationExpenseID DESC; -- Totals by month (TransactionDate) SELECT CONVERT(char(7), f.TransactionDate, 120) AS TrxMonth, COUNT(*) AS LineCount, COUNT(DISTINCT f.TEFileNo) AS TransfereeCount, SUM(f.Amount) AS TotalAmount FROM dbo.FactRelocationExpense f WHERE f.TransactionDate IS NOT NULL GROUP BY CONVERT(char(7), f.TransactionDate, 120) ORDER BY TrxMonth DESC; -- Flag distribution SELECT SUM(CASE WHEN ISNULL(f.DeniedExpense, 0) = 1 THEN 1 ELSE 0 END) AS DeniedLines, SUM(CASE WHEN ISNULL(f.TrackOnly, 0) = 1 THEN 1 ELSE 0 END) AS TrackOnlyLines, SUM(CASE WHEN ISNULL(f.ExceptionLine, 0) = 1 THEN 1 ELSE 0 END) AS ExceptionLines, SUM(CASE WHEN ISNULL(f.CategoryExcludeFromStdReports, 0) = 1 OR ISNULL(f.ItemExcludeFromStdReports, 0) = 1 THEN 1 ELSE 0 END) AS ExcludedStdReportLines, SUM(CASE WHEN ISNULL(f.CategoryHomeSaleCosts, 0) = 1 OR ISNULL(f.ItemHomeSaleCosts, 0) = 1 THEN 1 ELSE 0 END) AS HSCCategoryLines, COUNT(*) AS TotalLines FROM dbo.FactRelocationExpense f;