/* Vendor Usage — summary (DatawarehouseNCM) Fact: dbo.FactVendorUsage Counts and distinct assignees rolled up by supplier and by client. */ DECLARE @UsageReportType varchar(20) = 'HHG'; -- HHG | TLV | MiscOrders DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; -- By supplier SELECT f.UsageReportType, f.VendorParentID, f.CompanyName, f.CompanySubName, COUNT(*) AS [OrderCount], COUNT(DISTINCT f.TEFileNo) AS [AssigneeCount], COUNT(DISTINCT f.CorpCode) AS [ClientCount] FROM dbo.FactVendorUsage f WHERE f.UsageReportType = @UsageReportType AND f.OrderDate BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) GROUP BY f.UsageReportType, f.VendorParentID, f.CompanyName, f.CompanySubName ORDER BY [OrderCount] DESC, f.CompanyName; -- By client SELECT f.UsageReportType, f.CorpCode, f.FormalName, COUNT(*) AS [OrderCount], COUNT(DISTINCT f.VendorParentID) AS [SupplierCount], COUNT(DISTINCT f.TEFileNo) AS [AssigneeCount] FROM dbo.FactVendorUsage f WHERE f.UsageReportType = @UsageReportType AND f.OrderDate BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) GROUP BY f.UsageReportType, f.CorpCode, f.FormalName ORDER BY [OrderCount] DESC, f.CorpCode;