--02 Supplier Diversity Spend Summary.sql -- SE rewrite of BO Supplier Diversity Spend summary -- Views: udv_transaction_detail_34, udv_service_detail_view_34 DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; ;WITH Spend AS ( SELECT NULL AS VendorParentID, -- Not used in report COALESCE(NULLIF(sd.[Supplier Name], ''), td.[Payee Name]) AS CompanyName, sd.[Desginations] AS DiversityCertification, CAST(NULL AS varchar(100)) AS SubClassDescr, -- Not used in report ISNULL(td.[Paid Amount], td.[Transaction Amount]) AS Amount 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 (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) 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 ) ) -- By supplier + certification SELECT VendorParentID, CompanyName, DiversityCertification, SubClassDescr, COUNT(*) AS [RowCount], SUM(ISNULL(Amount, 0)) AS TotalSpend FROM Spend GROUP BY VendorParentID, CompanyName, DiversityCertification, SubClassDescr ORDER BY TotalSpend DESC, CompanyName; -- By certification only SELECT DiversityCertification, COUNT(DISTINCT CompanyName) AS SupplierCount, SUM(ISNULL(Amount, 0)) AS TotalSpend FROM Spend GROUP BY DiversityCertification ORDER BY TotalSpend DESC;