--01 Revenue By State Detail.sql -- SE rewrite of BO Revenue By State detail (FactRevenueByState) -- Views: udv_transaction_detail_34, udv_profile_information_view_34 -- Note: ActNumbr1-4 / CodeId / BO state resolution rules are not in SEViews. DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @State varchar(50) = NULL; SELECT NULL AS ActNumbr1, -- unable to find NULL AS ActNumbr2, -- unable to find NULL AS ActNumbr3, -- unable to find NULL AS ActNumbr4, -- unable to find td.[File #] AS GroupId, td.[Last Name] AS TELastName, td.[Invoice Date] AS DocDate, -- or Financial Date NULL AS CodeId, -- unable to find td.[Transaction Amount] AS Amount, pf.[Dest Work City] AS City, -- BO used dept vs dest by ActNumbr3 pf.[Dest Work State] AS State, td.[Corp Code] AS CorpCode, td.[Expense Item] AS ActDescr, -- approximate td.[Source Type] AS Source 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 ISNULL(td.[Invoice Date], td.[Financial Date]) BETWEEN @StartDate AND @EndDate AND (@State IS NULL OR @State = '' OR pf.[Dest Work State] = @State) ORDER BY ISNULL(td.[Invoice Date], td.[Financial Date]) DESC, td.[File #], td.[Last Name], pf.[Dest Work City], pf.[Dest Work State];