Chris.Kendrick of Team Echo Idea
10/29/2018 - 2:09 PM
TL Solutions - Profitability Analysis
when we receive a primary award, run this script to get some of the required inputs. Other inputs fro from RFP Pricing Engine and/or award file.
What: TL Solutions script to populate Profitability Analysis reports Where: n/a - based on RFP Why: show RFP/Account historic information When: past year How: EchoOptimizer tables, TLPricing tables
--=================================================
-- Creator: Chris Kendrick
-- Purpose: Populate data needed for TL RFP Profitability Analyses
-- Created: 12.21.2017
-- Updated: 3.12.2018
-- Tender Type Segment: 10.8.2018
-- Email list added: 10.26.2018
--=================================================
--variables
DECLARE @startdate as date = dateadd(year,-1,GETDATE()); --how far back actuals goes
DECLARE @enddate as date = GETDATE(); --how far back actuals stops
DECLARE @bidId as varchar(20) = '0064A00000uZiKvQAK'; --required input, identifies the RFP record from TLPricing.rfp.bid_history
DECLARE @salesRepEmail as varchar(50) = --gets the reps email address to then use talentmart.dim.employee
(
select distinct
U.emailId
from tlpricing.rfp.bid_history as B
left join EchoOptimizer.dbo.tblUsers as U on B.SalesRep = U.FullName
where bidid = @bidId
and U.[Status] = 'A'
);
--------------------------------------------------------------------------------------------------------------------------
--Email output
--copy/paste this into the PA email adress
--------------------------------------------------------------------------------------------------------------------------
select
R.email as repEmail
,';' as [;]
,IIF(M1.Email = 'Sean.Burke@echo.com','',IIF(M1.email = 'dmenzel@echo.com','',IIF(M1.email = 'Doug@echo.com','',M1.Email))) as Manager1Email
,';' as [;]
,IIF(M2.Email = 'Sean.Burke@echo.com','',IIF(M2.email = 'dmenzel@echo.com','',IIF(M2.email = 'Doug@echo.com','',M2.Email))) as Manager2Email
,';' as [;]
,IIF(M3.Email = 'Sean.Burke@echo.com','',IIF(M3.email = 'dmenzel@echo.com','',IIF(M3.email = 'Doug@echo.com','',M3.Email))) as Manager3Email
,';' as [;]
,'RFPProfitanalaysis@echo.com' as ProfitDistList
from TalentMart.dim.employee as R
left join TalentMart.dim.employee as M1 on R.Supervisor1ID = M1.userId
left join TalentMart.dim.employee as M2 on R.Supervisor2ID = M2.userId
left join TalentMart.dim.employee as M3 on R.Supervisor3ID = M3.userId
where R.email = @salesRepEmail
and R.OrgLevel1 = 'Sales'
and LEFT(R.OrgLevel2,7) <> 'Carrier'
and R.isCurrentRow = 1
and M1.isCurrentRow = 1
and M2.isCurrentRow = 1
and M3.isCurrentRow = 1
;
--------------------------------------------------------------------------------------------------------------------------
--CREATING #primaryDeets
--ouputs bid level information
--------------------------------------------------------------------------------------------------------------------------
if object_id('tempdb.db.#primaryDeets', 'U') is not null drop table #primaryDeets;
SELECT
b.CustomerName,b.BidId,b.Enumber,b.BU,b.GoLiveDate,b.RateExpDate
INTO #primaryDeets
FROM tlpricing.rfp.bid_history as B
WHERE BidId = @bidId
;--select * from #primaryDeets
select * from #primaryDeets
;
--------------------------------------------------------------------------------------------------------------------------
--creating #bid
--setting up the hunt for actuals
--------------------------------------------------------------------------------------------------------------------------
if object_id('tempdb.db.#bids', 'U') is not null drop table #bids;
with cte as (
select B.CustomerName,B.BidId,B.GoLiveDate,B.RateExpDate,BidType,ClientType,b.Enumber
from TLPricing.rfp.Bid_History as b
where B.Bidid = @bidId
)
select
Distinct CTE.BidId
,CTE.GoLiveDate
,CTE.RateExpDate
,CTE.BidType
,CTE.ClientType
,CASE
WHEN C.ParentAccountId is null then C.CustomerId
WHEN C.ParentAccountId = '5F8A8313-31D4-49E2-83F2-2836244C5216' then C.CustomerId
else PC.CustomerId end as ChildEnumbers
INTO #bids
from cte
LEFT JOIN EchoOptimizer.dbo.tblCustomer as C ON cte.Enumber = C.CustomerId
LEFT JOIN EchoOptimizer.dbo.tblCustomer as PC ON C.ParentAccountId = PC.ParentAccountId
; --select * from #bids
--------------------------------------------------------------------------------------------------------------------------
--CREATING #loads
-- restrincts to loads CREATED before the @Date week's Sunday (only changes once/week)
-- continuing hunt for actuals
--------------------------------------------------------------------------------------------------------------------------
if object_id('tempdb.db.#loads', 'U') is not null drop table #loads;
SELECT
L.LoadGuId
,C.CustomerId
,L.Revenue
,L.Cogs
,L.Revenue - L.Cogs as GP
,L.CreatedDate
,O.City + ' ' + o.StateCode as originCityState
,d.City + ' ' + d.StateCode as DestCityState
INTO #loads
FROM EchoOptimizer.dbo.tblLoads as L
left join EchoOptimizer.dbo.tblAddress as O on L.OriginAddressId = O.AddressId
left join EchoOptimizer.dbo.tblAddress as D on L.DestinationAddressId = D.AddressId
LEFT JOIN EchoOptimizer.dbo.tblCustomerLoads as CL ON L.LoadGuid = CL.LoadGuid
LEFT JOIN EchoOptimizer.dbo.tblCustomer as C ON CL.CustomerGuid = C.CustomerGuid
INNER JOIN (SELECT distinct ChildEnumbers from #bids) as SUB ON C.CustomerId = SUB.ChildEnumbers
WHERE L.Mode = 'TL'
AND L.[Status] = 'Delivered'
AND L.CreatedDate between @startdate and @enddate
and L.COgs > 150
GROUP BY L.LoadGuId,C.CustomerId,L.Revenue,L.Cogs,L.Revenue - L.Cogs,L.CreatedDate
,O.City + ' ' + o.StateCode
,d.City + ' ' + d.StateCode
HAVING COUNT(*) = 1 --removing any dups
; --select * from #loads
--------------------------------------------------------------------------------------------------------------------------
--TenderType (#TenderType)
-- getting a tender type for each historical shipment
--------------------------------------------------------------------------------------------------------------------------
if object_id('tempdb.db.#tenderType', 'U') is not null drop table #tenderType;
SELECT
L.LoadGuid
,CASE WHEN RefField.refvalue IN ('Award','Award (PreSpot)','Award (Pre-Spot)') THEN 'Award'
WHEN RefField.refValue IN ('Routing Guide') THEN 'Routing Guide'
WHEN RefField.refValue IN ('Spot') THEN 'Spot'
WHEN RefField.refValue IS NULL AND CAST(L.CreatedDate AS DATE) < '1/25/2017' THEN 'Other'
ELSE (CASE WHEN LE.TenderTypeId = 1 THEN 'Spot'
WHEN LE.TenderTypeId = 2 THEN 'Award'
WHEN LE.TenderTypeId = 3 THEN 'Routing Guide'
WHEN LE.TenderTypeId = 4 THEN 'Spot'
ELSE 'Spot'
END)
END AS TenderType
--,LE.CreatedDate as dropDownCreatedDate
-- ,RefField.CreatedDate as refFieldCreatedDate
,RANK() OVER (partition by L.LoadGuid order by RefField.CreatedDate DESC) as RNK
INTO #tenderType
FROM #Loads AS L
LEFT JOIN EchoOptimizer.dbo.tblLoadsExtended AS LE WITH(NoLock)
ON L.LoadGuId = LE.LoadGuid
LEFT JOIN (
SELECT DISTINCT LR.LoadGuId, LR.refValue,LR.Createddate
FROM EchoOptimizer.dbo.LoadReference AS LR (nolock)
INNER JOIN EchoOptimizer.dbo.RefType AS RT (nolock)
ON LR.refTypeID = RT.refTypeID
WHERE (RT.refTypeName LIKE 'Tender Type%' OR RT.refTypeName LIKE 'Tende Type%')
AND LR.RecStatus = 0
AND RT.RecStatus = 0
--and LoadGuid = '4BC0195D-98A3-46FC-94DB-3AD1A9F0A56C'
) AS RefField
ON L.LoadGuId = RefField.loadguid
;--select * from #tenderType order by LoadGuid,RNK ASC
/* Removing duplicated tender type reference field loads*/
delete from #tenderType
where RNK >1
;--select * from #tenderType order by LoadGuid,RNK ASC
/* --dup check
select LoadGuid
from #tenderType
group by LoadGuid
HAVING COUNT(*) >1
*/
--------------------------------------------------------------------------------------------------------------------------
--Actuals output
--...outputs the actuals - use this in the upper left box
--------------------------------------------------------------------------------------------------------------------------
WITH CTE AS (
--overall
select
'Overall' as TenderType
,0 as ordr
,COUNT(Distinct L.originCityState+L.DestCityState) as lanes
,COUNT(*) as volume
,SUM(L.revenue) as Revenue
,SUM(L.GP) as GP
from #loads as L
inner join #tenderType as T on L.LoadGuid = T.LoadGuid
UNION ALL
--primary
select
'Primary' as TenderType
,1 as ordr
,COUNT(Distinct L.originCityState+L.DestCityState) as lanes
,COUNT(*) as volume
,SUM(L.revenue) as Revenue
,SUM(L.GP) as GP
from #loads as L
inner join #tenderType as T on L.LoadGuid = T.LoadGuid
where T.TenderType = 'Award'
UNION ALL
--RG
select
'Routing Guide' as TenderType
,2 as ordr
,COUNT(Distinct L.originCityState+L.DestCityState) as lanes
,COUNT(*) as volume
,SUM(L.revenue) as Revenue
,SUM(L.GP) as GP
from #loads as L
inner join #tenderType as T on L.LoadGuid = T.LoadGuid
where T.TenderType = 'Routing Guide'
UNION ALL
--RG
select
'Spot/Other' as TenderType
,3 as ordr
,COUNT(Distinct L.originCityState+L.DestCityState) as lanes
,COUNT(*) as volume
,SUM(L.revenue) as Revenue
,SUM(L.GP) as GP
from #loads as L
inner join #tenderType as T on L.LoadGuid = T.LoadGuid
where T.TenderType not in ('Award', 'Routing Guide')
)
select
TenderType
,Lanes
,Volume
,cast(Revenue as int) as Total_Revenue
,cast(GP as int) as Total_GP
,CAST(CAST(GP as decimal(11,2))/CAST(Revenue as decimal(11,2))as decimal(7,3)) as [GP%]
,CAST(CAST(GP as decimal(11,2))/CAST(Volume as decimal(11,2))as decimal(11,2)) as [GP_per_Load]
from Cte
order by ordr
--------------------------------------------------------------------------------------------------------------------------
--Creating #pastRFPs
-- how did a "comparable" previous rfp's award look
--------------------------------------------------------------------------------------------------------------------------
if object_id('tempdb.db.#pastRFPs', 'U') is not null drop table #pastRFPs;
with cte as (
select
BidId
,GoLiveDate
,RateExpDate
,CASE
WHEN C.ParentAccountId is null then C.CustomerId
WHEN C.ParentAccountId = '5F8A8313-31D4-49E2-83F2-2836244C5216' then C.CustomerId
else PC.CustomerId end as ChildEnumbers
from #primaryDeets as PD
LEFT JOIN EchoOptimizer.dbo.tblCustomer as C ON PD.Enumber = C.CustomerId
LEFT JOIN EchoOptimizer.dbo.tblCustomer as PC ON C.ParentAccountId = PC.ParentAccountId
)
, pastBids as (
select CTE.BidId as 'CurrentBidID',CTE.GoLiveDate as 'CurrentGoLive',CTE.RateExpDate as 'CurrentRateExp',
b.BidId as 'PastBidID',b.GoLiveDate as 'PastGoLive',b.RateExpDate as 'PastRateExp'
from CTE
left join tlpricing.rfp.bid_history as b
on cte.ChildEnumbers = b.ENumber
and CTE.BidId <> b.BidId
and b.GoliveDate between DATEADD(d,-385,CTE.GoLiveDate) and DATEADD(d,-335,CTE.GoLiveDate)
and b.RateExpDate between DATEADD(d,-385,CTE.RateExpDate) and DATEADD(d,-335,CTE.RateExpDate)
where cte.ChildEnumbers is not null
and b.Bidid is not null
)
,AGG as (
select p.CurrentBidID,p.PastBidId
,SUM(CASE WHEN L.Award = 'Primary' then 1 else 0 end) as primaryCt
,CAST(SUM(CASE WHEN L.Award = 'Primary' then L.TotalExpectedVolume else 0 end) as int) as primaryVol
,CAST(SUM(CASE WHEN L.Award = 'Primary' then L.TotalExpectedRevenue else 0 end) as int) as primaryRev
,CAST(SUM(CASE WHEN L.Award = 'Primary' then L.TotalExpectedGP else 0 end) as int) as primaryGP
,CAST(SUM(CASE WHEN L.Award = 'Primary' then L.TotalExpectedGP else 0 end) / SUM(CASE WHEN L.Award = 'Primary' then L.TotalExpectedVolume else 0 end) as int)as primaryGPperLoad
,CAST(CAST(SUM(CASE WHEN L.Award = 'Primary' then L.TotalExpectedGP else 0 end) as decimal(15,3)) / CAST(SUM(CASE WHEN L.Award = 'Primary' then L.TotalExpectedRevenue else 0 end)as decimal(15,3)) as decimal(15,3)) as primaryGPpercent
from pastBids as p
left join tlpricing.rfp.lane_history as l
on p.PastBidID = L.BidId
Where l.award = 'Primary'
group by p.CurrentBidID,p.PastBidId
)
select
'Previous_RFP_Award' as category
,p.BidId
,AGG.primaryCt
,AGG.primaryVol
,AGG.primaryRev
,AGG.primaryGP
,AGG.primaryGPpercent
,AGG.primaryGPperLoad
into #pastRFPs
from #primaryDeets as p
left join AGG on p.BidId = AGG.CurrentBidID
;
select * from #pastRFPs;