/* Relocation Expense — summary (DatawarehouseNCM) Fact: dbo.FactRelocationExpense Crystal: ReloExpenseRptByTransfereeSummary.rpt TE / category rollup with the same filter parameters as the detail query. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @DivisionName varchar(30) = NULL; DECLARE @ReloPolicy varchar(50) = NULL; DECLARE @TEFileNo varchar(14) = NULL; DECLARE @ItemID int = NULL; DECLARE @DateType char(1) = 'T'; DECLARE @PaidTo smallint = NULL; DECLARE @IncludeDenied bit = 0; DECLARE @DeniedOnly bit = 0; DECLARE @ExcludeTrackOnly bit = 1; DECLARE @TrackOnly bit = 0; DECLARE @ExceptionsOnly bit = 0; DECLARE @ExcludeStdReports bit = 1; DECLARE @ExcludeHSCCategory bit = 0; SELECT f.CorpCode, f.FormalName, f.TEFileNo, f.TELastName, f.TEFirstName, f.DivisionName, f.ReloPolicy, f.CategoryID, f.Category, COUNT(*) AS LineCount, SUM(f.Amount) AS TotalAmount FROM dbo.FactRelocationExpense f WHERE ( (@TEFileNo IS NOT NULL AND @TEFileNo <> '' AND f.TEFileNo = @TEFileNo) OR ( (@TEFileNo IS NULL OR @TEFileNo = '') AND ( (@ParentRollup = 0 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode)) OR (@ParentRollup = 1 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.ParentCompany = @CorpCode OR f.CorpCode = @CorpCode)) ) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName IN ('(All Divisions)', '(All Studios)') OR f.DivisionName = @DivisionName ) AND ( @ReloPolicy IS NULL OR @ReloPolicy = '' OR @ReloPolicy = '(All Policies)' OR f.ReloPolicy = @ReloPolicy ) ) ) AND ( (@DateType = 'M' AND f.MoveComplete BETWEEN @StartDate AND @EndDate) OR (@DateType = 'P' AND f.SentToPayroll BETWEEN @StartDate AND @EndDate) OR (@DateType = 'T' AND f.TransactionDate BETWEEN @StartDate AND @EndDate) ) AND (@ItemID IS NULL OR @ItemID < 0 OR f.ItemID = @ItemID) AND (@PaidTo IS NULL OR f.PaidTo = @PaidTo) AND ( (@DeniedOnly = 1 AND f.DeniedExpense = 1) OR (@DeniedOnly = 0 AND (@IncludeDenied = 1 OR ISNULL(f.DeniedExpense, 0) = 0)) ) AND (@ExcludeTrackOnly = 0 OR ISNULL(f.TrackOnly, 0) <> 1) AND (@TrackOnly = 0 OR f.TrackOnly = 1) AND ( @ExcludeStdReports = 0 OR ( ISNULL(f.CategoryExcludeFromStdReports, 0) = 0 AND ISNULL(f.ItemExcludeFromStdReports, 0) = 0 ) ) AND ( @ExcludeHSCCategory = 0 OR ( ISNULL(f.CategoryHomeSaleCosts, 0) = 0 AND ISNULL(f.ItemHomeSaleCosts, 0) = 0 ) ) AND ( @ExceptionsOnly = 0 OR ( f.ExceptionLine = 1 AND ( f.ExceptionAppliesTo = 0 OR ((f.ExceptionAppliesTo IN (1, 3) AND ISNULL(f.GrossUpCategory, 0) = 0)) OR ((f.ExceptionAppliesTo IN (1, 2) AND ISNULL(f.GrossUpCategory, 0) = 1)) ) AND ISNULL(f.WithholdingCategory, 0) = 0 ) ) GROUP BY f.CorpCode, f.FormalName, f.TEFileNo, f.TELastName, f.TEFirstName, f.DivisionName, f.ReloPolicy, f.CategoryID, f.Category ORDER BY f.CorpCode, f.TELastName, f.TEFirstName, f.TEFileNo, f.Category; -- Client totals SELECT f.CorpCode, f.FormalName, COUNT(*) AS LineCount, COUNT(DISTINCT f.TEFileNo) AS TransfereeCount, SUM(f.Amount) AS TotalAmount FROM dbo.FactRelocationExpense f WHERE ( (@DateType = 'M' AND f.MoveComplete BETWEEN @StartDate AND @EndDate) OR (@DateType = 'P' AND f.SentToPayroll BETWEEN @StartDate AND @EndDate) OR (@DateType = 'T' AND f.TransactionDate BETWEEN @StartDate AND @EndDate) ) AND ( (@ParentRollup = 0 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode)) OR (@ParentRollup = 1 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.ParentCompany = @CorpCode OR f.CorpCode = @CorpCode)) ) AND ( @ExcludeStdReports = 0 OR ( ISNULL(f.CategoryExcludeFromStdReports, 0) = 0 AND ISNULL(f.ItemExcludeFromStdReports, 0) = 0 ) ) AND (@ExcludeTrackOnly = 0 OR ISNULL(f.TrackOnly, 0) <> 1) AND ( (@DeniedOnly = 1 AND f.DeniedExpense = 1) OR (@DeniedOnly = 0 AND (@IncludeDenied = 1 OR ISNULL(f.DeniedExpense, 0) = 0)) ) GROUP BY f.CorpCode, f.FormalName ORDER BY TotalAmount DESC;