/* Supplier Diversity Spend — summary (DatawarehouseNCM) Fact: dbo.FactSupplierDiversitySpend Total spend rolled up by supplier (vendor parent) and diversity certification. Matches the report's summary mode. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; -- NULL / '' = All clients -- By supplier + certification SELECT f.VendorParentID, f.CompanyName, f.DiversityCertification, f.SubClassDescr, COUNT(*) AS [RowCount], SUM(f.Amount) AS TotalSpend FROM dbo.FactSupplierDiversitySpend f WHERE f.TransactionDate BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) GROUP BY f.VendorParentID, f.CompanyName, f.DiversityCertification, f.SubClassDescr ORDER BY TotalSpend DESC, f.CompanyName; -- By certification only SELECT f.DiversityCertification, COUNT(DISTINCT f.VendorParentID) AS SupplierCount, SUM(f.Amount) AS TotalSpend FROM dbo.FactSupplierDiversitySpend f WHERE f.TransactionDate BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) GROUP BY f.DiversityCertification ORDER BY TotalSpend DESC;