--02 Relocation Expense Summary.sql -- SE rewrite of BO Relocation Expense summary -- Views: udv_transaction_detail_34, udv_profile_information_view_34 DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; DECLARE @DivisionName varchar(30) = NULL; DECLARE @ReloPolicy varchar(100) = NULL; DECLARE @TEFileNo varchar(50) = NULL; DECLARE @DateType char(1) = 'T'; DECLARE @ExcludeTrackOnly bit = 1; DECLARE @TrackOnly bit = 0; DECLARE @ExceptionsOnly bit = 0; SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, td.[File #] AS TEFileNo, td.[Last Name] AS TELastName, td.[First Name] AS TEFirstName, td.[Division] AS DivisionName, td.[Policy] AS ReloPolicy, NULL AS CategoryID, -- Not used in Report td.[Expense Category] AS Category, COUNT(*) AS LineCount, SUM(ISNULL(td.[Transaction Amount], 0)) AS TotalAmount FROM se_reports.dbo.[udv_transaction_detail_34] td JOIN se_reports.dbo.[udv_profile_information_view_34] pf ON td.[File #] = pf.[File #] WHERE ( (@TEFileNo IS NOT NULL AND @TEFileNo <> '' AND td.[File #] = @TEFileNo) OR ( (@TEFileNo IS NULL OR @TEFileNo = '') AND (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName IN ('(All Divisions)', '(All Studios)') OR td.[Division] = @DivisionName ) AND ( @ReloPolicy IS NULL OR @ReloPolicy = '' OR @ReloPolicy = '(All Policies)' OR td.[Policy] = @ReloPolicy ) ) ) AND ( (@DateType = 'M' AND td.[Move Complete Date] BETWEEN @StartDate AND @EndDate) OR (@DateType = 'T' AND td.[Transaction Date] BETWEEN @StartDate AND @EndDate) OR (@DateType = 'P' AND td.[Payroll Batch Date] BETWEEN @StartDate AND @EndDate) ) AND (@ExcludeTrackOnly = 0 OR CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END <> 1) AND (@TrackOnly = 0 OR CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END = 1) AND (@ExceptionsOnly = 0 OR td.[Exception] = 'Yes') GROUP BY td.[Corp Code], td.[Org Name], td.[File #], td.[Last Name], td.[First Name], td.[Division], td.[Policy], td.[Expense Category] ORDER BY td.[Corp Code], td.[Last Name], td.[First Name], td.[File #], td.[Expense Category];