M.Danczyk of Variable Finder
3/19/2019 - 9:13 PM
Carrier Profile Details (Optimizer)
Carrier Profile details obtained from Optimizer. Clutch profile details may differ.
--=================================================
-- Creator: Megan Danczyk
-- Created Date: 3/19/19
-- Modified Date: --
-- Purpose: Identify Carrier Profile Details for Network Analysis
-- Modified Reason:
--=================================================
SELECT
--Company Information
CAR.CarrierName AS 'Carrier Name'
,CAR.CarrierId AS 'Carrier Id'
,CAR.CarrierGuid
,CAR.CreatedDate
,CAR.DBA AS 'Doing Business As (DBA)'
,CAR.FID
,CASE WHEN CW.SafetyRatingID = 1 THEN 'Not On File'
WHEN CW.SafetyRatingID = 2 THEN 'Satisfactory'
WHEN CW.SafetyRatingID = 3 THEN 'Conditional'
WHEN CW.SafetyRatingID = 4 THEN 'Unsatisfactory'
END AS 'FMCSA Rating'
,CW.SafetyRatingDate AS 'Safety Rating Date'
--Certification
,CAR.USDOT AS 'USDOT #'
,CARE.DocketPrefix AS 'Docket Prefix'
,CAR.MCNumber AS 'Docket #'
,CAR.IntraState
,CAR.CarrierLtlATMigrationState AS 'State'
--Carrier Business Information
,CARAA.Phone AS 'Main Phone (Ext)'
,CARAA.Address1 AS 'Address'
,CARA.City AS 'Carrier City'
,CARA.StateCode AS 'Carrier State'
,CARA.Zip AS 'Carrier Zip'
,CARC.CountryCD AS 'Carrier Country'
,CARA.Phone2 AS 'Secondary Phone (Ext)'
,CARA.Fax AS 'Fax'
,CARA.Email AS 'Email'
,CARA.WebSite AS 'Website'
--General Infomration
,VT.Name AS 'Vendor Type'
,CASE WHEN CAR.CurrencyTypeID = 1 THEN 'USD' ELSE 'CAN' END AS 'Currency Type'
,CAR.SCAC
--AP Rep is null
--Carrier Ownership
,U.FullName AS 'Carrier Owner'
,U.status AS 'Carrier Owner Status'
,O.office AS 'Carrier Owner Office'
,B.name AS 'Carrier Owner Business Unit'
,TCPOT.OwnershipStatusType AS 'Ownership Status'
--Profile Business Information
,CARA.Address1
,CONCAT(CARA.City,', ',CARA.StateCode) AS 'profile_City/State'
,CONCAT(CARA.Zip,', ',CARC.CountryCD) AS 'profile_Zip/Country'
--Contact Information
,CAR.ContactName
--Title
,CAR.ContactPhone
,CARAA.Fax AS 'Contact Fax'
,CARAA.Email AS 'Contact Email'
--Title
--Notes
--Logo
--Insurance
,CAR.LiabilityLimit AS 'General Liability'
,CAR.CargoLimit AS 'Cargo Liability'
,CARE.AutoLimit AS 'Auto Liability'
,CAR.InsuranceOnFile AS 'Insurance on File'
,CAR.InsuranceExpiryDate AS 'Insurance Exp Date'
,CASE WHEN CAR.PacketOnFile = 1 THEN 'Yes' ELSE 'No' END AS 'Packet on File'
--PacketOnFileDate
,CASE WHEN CAR.CertificateHolder = 1 THEN 'Yes' ELSE 'No' END AS 'Certificate Holder'
,CASE WHEN CAR.HazMat = 1 THEN 'Yes' ELSE 'No' END AS 'Haz Mat Certified'
,CAR.HazMatRegCode AS 'Haz Mat Reg #'
,CAR.HazMatExpires AS 'Haz Mat Expiry Date'
,CAR.HazMatDrivers AS 'Haz Mat Drivers'
,CASE WHEN CARE.IsTargetedCommodityApproved = 1 THEN 'Yes' ELSE 'No' END AS 'Approved Targeted Commodity Carrier'
--Tracking Preferences
,CASE WHEN CAR.TrackingTypeID = 1 THEN 'Standard Tracking'
WHEN CAR.TrackingTypeID = 2 THEN 'Web Tracking Only'
WHEN CAR.TrackingTypeID = 3 THEN 'Do not Track'
WHEN CAR.TrackingTypeID = 4 THEN 'MacroPoint-ELD Tracking'
WHEN CAR.TrackingTypeID = 5 THEN 'MacroPoint-Cell/App Tracking'
WHEN CAR.TrackingTypeID = 6 THEN 'MacroPoint-Trailer Tracking'
WHEN CAR.TrackingTypeID = 7 THEN 'MacroPoint-TMS Tracking'
WHEN CAR.TrackingTypeID = 8 THEN 'EchoDrive'
END AS 'Tracking Type'
,CAR.TrackingResource
,CARE.IsTargetedCommodityApproved
--Carrier History
,CAST(CAR.CreatedDate AS DATE) AS 'Joined Date'
,Carl.LastBooked AS 'Last Load Booked Date'
,Carl.FirstBooked AS 'First Load Booked Date'
,CARL.TotalLoads AS 'Total Load Volume'
,CARL.Past90Days AS 'Loads Booked Last 90 Days'
--Carrier Status
,CAR.Status
,CAR.StatusChangeDate AS 'Status Last Change'
--Carrier Equipment Profile
,CEP.PowerUnits AS 'Power Units'
--Trailers
,CEP.Drivers AS 'Company Drivers'
,CEP.OwnerOperators AS 'Owner Operators'
,CMT.LTL
,CMT.Partial
,CMT.Drayage
,CMT.[Small Parcel]
,CMT.TL
,CMT.Intermodal
,CMT.International
,CMT.Air
,CSS.[Team Services]
,CSS.[Load Bar]
,CSS.[Pallet Jack]
,CSS.[PAPS/FAST Approved]
,CSS.[Bulkhead (Multi-Temp)]
,CSS.[Blanket Wrapped]
,CSS.Straps
,CSS.[TWIC Card]
,CSS.OD
,CSS.Blankets
,CSS.[Driver Assist]
,CSS.[Drop Trailer Receiver]
,CSS.[Temperature Requirements]
,CSS.[Pallet Exchange]
,CSS.[Drop Trailer Shipper]
,CSS.Tarps
,CE.Van
,CE.[Flatbed/Deck]
,CE.Reefer
,CE.Tanker
,CE.Container
,CE.Specialized
,CE.Trailer
,CE.Box
,CE.[Total Trailers]
,CASE WHEN CARE.CarrierPaymentTypeId = 1 THEN 'Check'
WHEN CARE.CarrierPaymentTypeId = 2 THEN 'ACH'
ELSE 'N/A'
END AS CarrierPaymentType
,CONCAT('Tier ',CARE.CarrierPaymentTermId) AS CarrierPaymentTerm
,Packet.DateCarrierPacketUploaded
FROM EchoOptimizer.dbo.tblcarrier AS CAR WITH(NoLock) -- Base Carrier Table
LEFT JOIN EchoOptimizer.dbo.tblAddress AS CARA WITH(NoLock) -- Carrier Address Table
ON CAR.AddressId = CARA.AddressId
LEFT JOIN EchoOptimizer.dbo.tblCountry AS CARC WITH(NoLock) -- Carrier Address Country Table
ON CARA.CountryId = CARC.CountryId
LEFT JOIN EchoOptimizer.Carrier.VendorType AS VT WITH(NoLock) -- Vendor Type
ON VT.VendorTypeID = CAR.VendorTypeID
LEFT JOIN EchoOptimizer.Ownership.TruckloadCarrierProfileOwnership AS TCPO WITH(NoLock) -- Truckload Ownership Table
ON TCPO.CarrierGuid = CAR.CarrierGuid
LEFT JOIN EchoOptimizer.Reference.TruckloadCarrierProfileOwnershipStatusType AS TCPOT WITH(Nolock) -- Truckload Ownership Status Table
ON TCPO.OwnershipStatusTypeId = TCPOT.OwnershipStatusTypeId
LEFT JOIN EchoLogin2.dbo.[User] AS EL2 WITH(NoLock) -- Truckload Ownership Status User Decode
ON TCPO.OwningRepGuid = EL2.UserID
LEFT JOIN EchoOptimizer.dbo.tblUsers AS U WITH(NoLock) -- Truckload Ownership User
ON EL2.Username = U.UserName
LEFT JOIN EchoOptimizer.dbo.tblOffices AS O WITH(NoLock) -- Truckload Ownership Office
ON U.OfficeId = O.OfficeId
LEFT JOIN EchoOptimizer.dbo.tblBusinessUnits AS B WITH(NoLock) -- Truckload Ownership Business Unit
ON O.BusinessUnitID = B.BusinessUnitID
LEFT JOIN EchoOptimizer.dbo.tblCarrierExtended AS CARE WITH(NoLock) --Carrier Extended Table
ON Car.CarrierGuid = CARE.CarrierGuid
LEFT JOIN EchoOptimizer.dbo.tblAddress AS CARAA WITH(NoLock) -- Profile Busin
ON CARE.CarrierAddressId = CARAA.AddressId
LEFT JOIN EchoOptimizer.dbo.tblAddress AS RemitA WITH(NoLock) -- Carrier Remit Address
ON CAR.RemitAddressId = RemitA.AddressId
LEFT JOIN EchoOptimizer.Carrier.CarrierWatch AS CW WITH(NoLock) -- Carrier Watch Table (FMCSA)
ON CAR.CarID = CW.CarID
LEFT JOIN
( SELECT CARL.CarrierGuId
,COUNT(L.LoadGuid) AS TotalLoads
,SUM(CASE WHEN L.BookedDate BETWEEN DATEADD(DAY,-91,GETDATE()) AND DATEADD(DAY,-1,GETDATE()) THEN 1 ELSE 0 END) AS Past90Days
,MIN(L.BookedDate) AS FirstBooked
,MAX(L.BookedDate) AS LastBooked
FROM EchoOptimizer.dbo.tblCarrierLoads AS CARL WITH(NoLock)
LEFT JOIN EchoOptimizer.dbo.tblLoads AS L WITH(NoLock)
ON Carl.Loadguid = L.Loadguid
GROUP BY CARL.CarrierGuId
) AS Carl
ON Car.CarrierGuid = CARL.CarrierGuId
LEFT JOIN EchoOptimizer.dbo.CarrierEquipmentProfiles AS CEP WITH(NoLock) --Carrier Equipment Profile (General Equipment Info)
ON Car.CarID = CEP.CarrierId
LEFT JOIN ( SELECT N.LocatsId AS CarrierGuid
,MAX(N.CreatedDate) AS DateCarrierPacketUploaded
FROM EchoOptimizer.dbo.tblNotes AS N WITH(NoLock)
WHERE N.Title = 'Packet'
GROUP BY N.LocatsId
) AS Packet
ON CAR.CarrierGuid = Packet.CarrierGuid
LEFT JOIN (
SELECT CMT.CarrierId
,SUM(CASE WHEN CMT.CarrierModeId = 1 THEN 1 ELSE 0 END) AS 'LTL'
,SUM(CASE WHEN CMT.CarrierModeId = 2 THEN 1 ELSE 0 END) AS 'Partial'
,SUM(CASE WHEN CMT.CarrierModeId = 3 THEN 1 ELSE 0 END) AS 'Drayage'
,SUM(CASE WHEN CMT.CarrierModeId = 4 THEN 1 ELSE 0 END) AS 'Small Parcel'
,SUM(CASE WHEN CMT.CarrierModeId = 5 THEN 1 ELSE 0 END) AS 'TL'
,SUM(CASE WHEN CMT.CarrierModeId = 6 THEN 1 ELSE 0 END) AS 'Intermodal'
,SUM(CASE WHEN CMT.CarrierModeId = 7 THEN 1 ELSE 0 END) AS 'International'
,SUM(CASE WHEN CMT.CarrierModeId = 8 THEN 1 ELSE 0 END) AS 'Air'
FROM EchoOptimizer.dbo.CarrierModeTypes AS CMT WITH(NoLock) -- Carrier Modes
GROUP BY CMT.CarrierId
) AS CMT
ON CMT.CarrierId = Car.CarID
LEFT JOIN (
SELECT CSS.CarrierId
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 1 THEN 1 ELSE 0 END) AS 'Team Services'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 2 THEN 1 ELSE 0 END) AS 'Blanket Wrapped'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 3 THEN 1 ELSE 0 END) AS 'Straps'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 4 THEN 1 ELSE 0 END) AS 'Pallet Exchange'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 5 THEN 1 ELSE 0 END) AS 'TWIC Card'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 6 THEN 1 ELSE 0 END) AS 'Drop Trailer Shipper'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 7 THEN 1 ELSE 0 END) AS 'OD'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 8 THEN 1 ELSE 0 END) AS 'Tarps'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 9 THEN 1 ELSE 0 END) AS 'Blankets'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 10 THEN 1 ELSE 0 END) AS 'Load Bar'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 11 THEN 1 ELSE 0 END) AS 'Driver Assist'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 12 THEN 1 ELSE 0 END) AS 'Pallet Jack'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 13 THEN 1 ELSE 0 END) AS 'Drop Trailer Receiver'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 14 THEN 1 ELSE 0 END) AS 'PAPS/FAST Approved'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 15 THEN 1 ELSE 0 END) AS 'Temperature Requirements'
,SUM(CASE WHEN CSS.CarrierSpecialServiceId = 16 THEN 1 ELSE 0 END) AS 'Bulkhead (Multi-Temp)'
FROM EchoOptimizer.dbo.CarrierSpecialServices AS CSS WITH(NoLock)
GROUP BY CSS.CarrierId
) AS CSS
ON CSS.CarrierId = CAR.CarID
LEFT JOIN (
SELECT CEQ.CarrierId
,SUM(CASE WHEN EC.Code = 1 THEN CEQ.Quantity END) AS 'Van'
,SUM(CASE WHEN EC.Code = 2 THEN CEQ.Quantity END) AS 'Flatbed/Deck'
,SUM(CASE WHEN EC.Code = 3 THEN CEQ.Quantity END) AS 'Reefer'
,SUM(CASE WHEN EC.Code = 4 THEN CEQ.Quantity END) AS 'Tanker'
,SUM(CASE WHEN EC.Code = 5 THEN CEQ.Quantity END) AS 'Container'
,SUM(CASE WHEN EC.Code = 6 THEN CEQ.Quantity END) AS 'Specialized'
,SUM(CASE WHEN EC.Code = 7 THEN CEQ.Quantity END) AS 'Trailer'
,SUM(CASE WHEN EC.Code = 8 THEN CEQ.Quantity END) AS 'Box'
,SUM(CEQ.Quantity) AS 'Total Trailers'
FROM EchoOptimizer.dbo.CarrierEquipmentQuantities AS CEQ WITH(Nolock)
inner join EchoOptimizer.mds.Equipment_Equipment AS EE WITH(Nolock)
ON CEQ.EquipmentCode = EE.Code
INNER JOIN EchoOptimizer.mds.Equipment_Category AS EC WITH(Nolock)
on ee.Category_Code = ec.Code
GROUP BY CEQ.CarrierId
) AS CE
ON CE.CarrierId = Car.CarID