--02 Revenue By State Totals.sql -- SE rewrite of BO Revenue By State totals -- Views: udv_transaction_detail_34, udv_profile_information_view_34 DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @State varchar(50) = NULL; -- By state SELECT pf.[Dest Work State] AS State, COUNT(*) AS [RowCount], SUM(ISNULL(td.[Transaction Amount], 0)) AS Amount 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) GROUP BY pf.[Dest Work State] ORDER BY Amount DESC, pf.[Dest Work State]; -- Grand total SELECT SUM(ISNULL(td.[Transaction Amount], 0)) AS SumAmount 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);