--03 Vendor Usage Fact Sample.sql -- SE sample / sanity check (replaces FactVendorUsage sample) ;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.[File #] AS TEFileNo, pf.[Last Name] AS TELastName, 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, CAST(NULL AS varchar(50)) AS VendorID, -- Not used in SE sd.[Service (Workflow Template Name)] AS ServiceCode, CASE WHEN sd.[Domestic or International] LIKE '%International%' THEN CAST(2 AS smallint) WHEN sd.[Domestic or International] LIKE '%Domestic%' THEN CAST(1 AS smallint) ELSE CAST(0 AS smallint) END AS ServiceType, sd.[Consultant] AS SrvcProvider, CASE WHEN sd.[Service (Workflow Template Name)] = 'Auto Shipment' THEN 1 ELSE 0 END AS HHGAutoShipment FROM se_reports.dbo.[udv_service_detail_view_34] sd JOIN se_reports.dbo.[udv_profile_information_view_34] pf ON sd.[File #] = pf.[File #] WHERE NULLIF(LTRIM(RTRIM(sd.[Supplier Name])), '') IS NOT NULL ) SELECT TOP 100 u.UsageReportType, u.CorpCode, u.TEFileNo, u.TELastName, u.OrderDate, u.ParentCompany, u.CompanyName, u.CompanySubName, u.VendorID, u.ServiceCode, u.ServiceType, u.SrvcProvider, u.HHGAutoShipment, NULL AS DimMainID -- Not used in SE FROM UsageRows u ORDER BY u.UsageReportType, u.OrderDate DESC, u.CorpCode; SELECT u.UsageReportType, COUNT(*) AS [RowCount], MIN(u.OrderDate) AS MinOrderDate, MAX(u.OrderDate) AS MaxOrderDate FROM UsageRows u GROUP BY u.UsageReportType ORDER BY u.UsageReportType; SELECT COUNT(*) AS TotalRows, SUM(CASE WHEN u.TEFileNo IS NULL THEN 1 ELSE 0 END) AS NullFileNo FROM UsageRows u;