--03 Supplier Diversity Spend Fact Sample.sql -- SE sample / sanity check (replaces FactSupplierDiversitySpend sample) DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; SELECT TOP 100 td.[Corp Code] AS CorpCode, td.[File #] AS TEFileNo, td.[Transaction Date] AS TransactionDate, COALESCE(NULLIF(sd.[Supplier Name], ''), td.[Payee Name]) AS CompanyName, NULL AS VendorId, -- not used in report td.[Payee Name] AS Payee, td.[Expense Category] AS Category, ISNULL(td.[Paid Amount], td.[Transaction Amount]) AS Amount, sd.[Desginations] AS DiversityCertification, NULL AS SubClassDescr, -- not used in report sd.[Supplier Type] AS VendorType FROM se_reports.dbo.[udv_transaction_detail_34] td LEFT JOIN se_reports.dbo.[udv_service_detail_view_34] sd ON sd.[File #] = td.[File #] AND NULLIF(LTRIM(RTRIM(sd.[Desginations])), '') IS NOT NULL WHERE td.[Transaction Date] BETWEEN @StartDate AND @EndDate AND CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END <> 1 AND ( td.[Pay To] = 'Organization' OR NULLIF(LTRIM(RTRIM(sd.[Desginations])), '') IS NOT NULL ) ORDER BY td.[Transaction Date] DESC, CompanyName; SELECT COUNT(*) AS [RowCount], SUM(ISNULL(td.[Paid Amount], td.[Transaction Amount])) AS TotalSpend FROM se_reports.dbo.[udv_transaction_detail_34] td LEFT JOIN se_reports.dbo.[udv_service_detail_view_34] sd ON sd.[File #] = td.[File #] AND NULLIF(LTRIM(RTRIM(sd.[Desginations])), '') IS NOT NULL WHERE td.[Transaction Date] BETWEEN @StartDate AND @EndDate AND CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END <> 1 AND ( td.[Pay To] = 'Organization' OR NULLIF(LTRIM(RTRIM(sd.[Desginations])), '') IS NOT NULL );