--02 Vendor Usage Summary.sql -- SE rewrite of BO Vendor Usage summary -- View: udv_service_detail_view_34 DECLARE @UsageReportType varchar(20) = 'HHG'; DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; ;WITH UsageRows AS ( SELECT CASE WHEN sd.[TLV Booking Status] IS NOT NULL OR sd.[TLV Move In Date] IS NOT NULL OR sd.[TLV Number of Properties] IS NOT NULL THEN 'TLV' WHEN sd.[Estimated Pack Start Date - Domestic] IS NOT NULL OR sd.[Actual Pack Start Date - Domestic] IS NOT NULL OR sd.[Estimated Pack Start Date - Surface] IS NOT NULL OR sd.[Estimated Pack Start Date - Air] IS NOT NULL THEN 'HHG' ELSE 'MiscOrders' END AS UsageReportType, sd.[Corp Code] AS CorpCode, sd.[Client Name] AS FormalName, sd.[File #] AS TEFileNo, COALESCE( sd.[Order Date], sd.[TLV Booking Status Date], sd.[Service Initiated Date], sd.[Service Active Date] ) AS OrderDate, sd.[Client Name] AS ParentCompany, sd.[Supplier Name] AS CompanyName, sd.[Desginations] AS CompanySubName FROM se_reports.dbo.[udv_service_detail_view_34] sd WHERE NULLIF(LTRIM(RTRIM(sd.[Supplier Name])), '') IS NOT NULL ) -- By supplier SELECT u.UsageReportType, u.ParentCompany, u.CompanyName, u.CompanySubName, COUNT(*) AS [OrderCount], COUNT(DISTINCT u.TEFileNo) AS [AssigneeCount], COUNT(DISTINCT u.CorpCode) AS [ClientCount] FROM UsageRows u WHERE u.UsageReportType = @UsageReportType AND u.OrderDate BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR u.CorpCode = @CorpCode) GROUP BY u.UsageReportType, u.ParentCompany, u.CompanyName, u.CompanySubName ORDER BY [OrderCount] DESC, u.CompanyName; -- By client SELECT u.UsageReportType, u.CorpCode, u.FormalName, COUNT(*) AS [OrderCount], COUNT(DISTINCT u.CompanyName) AS [SupplierCount], COUNT(DISTINCT u.TEFileNo) AS [AssigneeCount] FROM UsageRows u WHERE u.UsageReportType = @UsageReportType AND u.OrderDate BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR u.CorpCode = @CorpCode) GROUP BY u.UsageReportType, u.CorpCode, u.FormalName ORDER BY [OrderCount] DESC, u.CorpCode;