Cacher is the code snippet organizer for pro developers

--=================================================
-- Creator: Megan Danczyk
-- Created Date: 3/19/19
-- Modified Date: --
-- Purpose: Data Dictionary for carrierremovalreasons (BOUNCES!). Most commonly used fields included with reason code logic.
-- Modified Reason: 
--=================================================

SELECT		TOP 100 
			CRR.LoadGuid -- Unique identifier readily joined to EchoOptimizer tblloads LoadGuid
			,CRR.CarrierLoadId -- Integer that joins to CarrierLoadId in tblCarrierLoads
			,CRR.CreatedDate -- Bounce Date
			,CRR.CarrierGuid -- Bounced CarrierGuid
			,CRR.CarrierName -- Bounced CarrierName
			,CRR.BookedWith -- Original Booking Rep of Bounced Carrier (Blame This Person)
			,CRR.CreatedBy -- Rep who removed carrier from load (Could be original booking rep, their manager, customer sales rep, or other)
			,CRR.NoOfHrsBeforeFirstPickUpCarrierRemoved -- Date Difference between Carrier Removal Date and Beginning of First Pickup Appointment (Calculation is incorrect. End Appt date should be used and time zones should be accounted for)
			,CRR.NoOfHrsBeforeFirstPickUpCarrierBooked -- Date Difference between original book date and the pickup date at the time the bounced carrier was booked. (Calculation is incorrect. End Appt should be used and time zones should be accounted for)
			,CRR.Notes -- Notes Entered By Bouncing Rep
			,CRR.CarrierBookedDate -- Original Book Date of carrier bounced (added per Data Team request in 2018)
			,CRR.FirstPickupByDate -- Original Pickup Date (CLDate) of load at the time bounced carrier was booked
			,LC.ReasonCode --Reason Code name for bounce ( )

			--Bounces within 5 hours of pickup are considered bad, regardless of reason code.

			--Bounces with reason codes (1,2,3,4,5,9,10,11,12,13,14,15,16,22,23,24) count against carrier.
			--Bounces with reason codes (7,21,26) count against Carrier Sales.
			--Bounces with reason codes (8,20,25) count against Client Sales/Ops.
			--Bounces with reason codes (6,17,18,28) count against Customer.
			--Bouncew with reason codes (19,27) are non issues.
			--Bounces with reason codes (1,2,3,4,5,7,8,9,10,11,12,13,14,15,16,19,21,23,24,25,26) count as "painful" to the Client 

FROM		EchoOptimizer.dbo.tblCarrierRemoveReasons CRR 
LEFT JOIN	EchoOptimizer.dbo.LookUpCarrierRemoveReasonCode LC ON CRR.CarrierRemoveReasonCodeID = LC.CarrierRemoveReasonCodeID