WITH DispatchStops AS(
SELECT
DispatchKey,
MAX( loader ) LoadAt_Customer,
MAX( loaderarea ) AreaCode_Pickup,
CASE WHEN MAX( tcall ) IS NULL THEN MAX( final ) ELSE MAX( tcall ) END Drop_Customer,
CASE WHEN MAX( tcall ) IS NULL THEN MAX( finalarea ) ELSE MAX( tcallarea ) END AreaCode_Drop,
MAX( TCALL ) TCALL,
MAX( arrivalcallcts ) arrivalcallcts
FROM
( SELECT
DispatchKey,
CASE WHEN Stopnumber = 1 THEN stopcustomercode END loader,
CASE WHEN Stopnumber = 90 THEN Stopcustomercode END final,
CASE WHEN Stopnumber = 99 THEN stopcustomercode END tcall,
CASE WHEN Stopnumber = 1 THEN PU_Area_Code END loaderarea,
CASE WHEN Stopnumber = 90 THEN PU_Area_Code END finalarea,
CASE WHEN Stopnumber = 99 THEN PU_Area_Code END tcallarea,
CASE WHEN StopNumber = 1 THEN CONVERT( DATETIME, arrivalcalltimestamputc ) AT TIME ZONE 'UTC' AT TIME ZONE 'Mountain Standard Time' END arrivalcallcts
FROM(
SELECT
CONCAT( ordernumber, '-', dispatchnumber ) DispatchKey, stopnumber, StopCustomerCode, PU_Area_Code, arrivalcalltimestamputc
FROM [mvtebi_dw_ops].[dw_ops].[platsci].[amqp_dispatchstops] amqp
LEFT JOIN ops.tbl_Dim_ICC_Customer Customer
ON amqp.stopcustomercode = Customer.Customer_Code AND Customer.ETL_Current_Row = 1
WHERE StopRemovedTimeStampUTC IS NULL
) AS CustomerMaster
) AS StopType
GROUP BY DispatchKey
), FactDisp AS (
SELECT
tractor.division,
Tractor_Code_Trimmed,
driver1.Driver_Code DriverCode1,
CASE WHEN Dispatch_Num = 1 THEN loaderarea.Area_Code ELSE AreaCode_pickup END RoutelinePickup,
CASE WHEN TCall IS NOT NULL THEN AreaCode_Drop WHEN Dispatch_Num = finaldispatch THEN dropperarea.Area_Code END RoutelineDrop,
CONCAT( fd.Order_Num, '-', Dispatch_Num ) DispatchKey,
CASE
WHEN DATEDIFF( HOUR, LAG( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), Dispatch_Datetime )>= 24
THEN Dispatch_Datetime
END NAIVESTART,
CASE
WHEN DATEDIFF( HOUR, Dispatch_End_Datetime, LEAD( Dispatch_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ))>= 24
THEN NULL
WHEN
DATEDIFF( HOUR, LAG( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), Dispatch_Datetime )>= 24 -- naive
OR
(
DATEDIFF( HOUR, LAG( Dispatch_Datetime, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime ), LAG( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime ))
-(
( CAST((( LAG( Total_Miles, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime )) / 565 ) AS INT )*10 )
+( CAST(( LAG( Total_Miles, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime ) / 565 ) AS INT )*11 )
+(
CAST(
11*((( CONVERT( DECIMAL( 8, 3 ), LAG( Total_miles, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime ))
/ CONVERT( DECIMAL( 8, 5 ), 565 ))) - ( LAG( Total_miles, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime ) / 565 )) AS INT
)
)
)
) >= 20
AND DATEDIFF( HOUR, LAG( Dispatch_End_Datetime, 2 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), LAG( Dispatch_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime )) < 20
AND DATEDIFF( HOUR, LEAD( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), LEAD( Dispatch_Datetime, 2 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime )) < 20
AND DATEDIFF( HOUR, LAG( Dispatch_Datetime, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime ), LAG( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime )) <= 20
THEN Dispatch_Datetime
END circuitstart,
CASE
WHEN
DATEDIFF( HOUR, LAG( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), Dispatch_Datetime )>= 24
THEN NULL
WHEN
( DATEDIFF( HOUR, Dispatch_End_Datetime, LEAD( Dispatch_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ))>= 24 )
THEN Dispatch_End_Datetime
WHEN
(
DATEDIFF( HOUR, Dispatch_Datetime, Dispatch_End_Datetime )
-(
( CAST(( Total_Miles / 565 ) AS INT ) * 10 )
+( CAST(( Total_Miles / 565 ) AS INT ) * 11 )
+( CAST( 11*((( CONVERT( DECIMAL( 8, 3 ), Total_Miles ) / CONVERT( DECIMAL( 8, 5 ), 565 ))) - ( Total_miles / 565 )) AS INT ))
)
) >= 24
AND DATEDIFF( HOUR, LAG( Dispatch_End_Datetime, 2 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), LAG( Dispatch_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ))<24
AND DATEDIFF( HOUR, LEAD( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), LEAD( Dispatch_Datetime, 2 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ))<24
AND ABS( DATEDIFF( HOUR, Dispatch_end_Datetime, Order_Late_Delivery_Appt )) >=5
OR
LEAD( Dispatch_datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ) IS NULL AND ( DATEDIFF( HOUR, Dispatch_End_Datetime, CURRENT_TIMESTAMP ) >= 24 )
THEN Dispatch_End_Datetime
END circuitend,
CASE
WHEN LEAD( Dispatch_datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ) IS NULL AND ( DATEDIFF( HOUR, Dispatch_End_Datetime, CURRENT_TIMESTAMP ) >= 24) THEN 'CASEB'
WHEN
(
DATEDIFF( HOUR, Dispatch_Datetime, Dispatch_End_Datetime )
-(
( CAST(( Total_Miles / 565 ) AS INT ) * 10 )
+( CAST(( Total_Miles / 565 ) AS INT ) * 11 )
+( CAST( 11*((( CONVERT( DECIMAL( 8, 3 ), Total_Miles ) / CONVERT( DECIMAL( 8, 5 ), 565 ))) - ( Total_miles / 565 )) AS INT ))
)
) >= 24
AND DATEDIFF( HOUR, LAG( Dispatch_End_Datetime, 2 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), LAG( Dispatch_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ))<24
AND DATEDIFF( HOUR, LEAD( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), LEAD( Dispatch_Datetime, 2 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ))<24
AND ABS( DATEDIFF( HOUR, Dispatch_end_Datetime, Order_Late_Delivery_Appt )) >=5
THEN 'CASEA'
END sakjfhnsaf,
CASE
WHEN DATEDIFF( HOUR, Dispatch_End_Datetime, LEAD( Dispatch_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime )) >= 24
THEN Dispatch_End_Datetime
END NAIVEEND,
DATEDIFF( HOUR, LAG( Dispatch_Datetime, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime ), LAG( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY Driver1.Driver_Code ORDER BY Dispatch_Datetime )) laddispdur,
DATEDIFF( HOUR, LAG( Dispatch_End_Datetime, 1 ) OVER( PARTITION BY driver1.Driver_Code ORDER BY Dispatch_Datetime ), Dispatch_Datetime ) durationbetweendispatch,
Dispatch_Datetime, Dispatch_End_Datetime,
Order_Late_Delivery_Appt delappt,
Total_Miles TotalMiles,
DATEDIFF( HOUR, Dispatch_datetime, Dispatch_end_Datetime ) Dispduration,
CONCAT( Driver1.City, ', ', Driver1.State ) DriverHome
FROM ops.tbl_Fact_ICC_Dispatch fd
LEFT JOIN ops.tbl_Dim_ICC_Customer loader
ON fd.SK_LoadAt_Customer_ID = loader.SK_Customer_ID
LEFT JOIN ops.tbl_Dim_ICC_Customer dropper
ON fd.SK_Consignee_Customer_ID = dropper.SK_Customer_ID
LEFT JOIN ops.tbl_Dim_ICC_Area loaderarea
ON fd.SK_Order_Pickup_Area_ID = loaderarea.SK_Area_ID
LEFT JOIN ops.tbl_Dim_ICC_Area dropperarea
ON fd.SK_Order_dest_Area_ID = dropperarea.SK_Area_ID
LEFT JOIN ops.tbl_Dim_ICC_Unit tractor
ON fd.SK_Unit_ID = tractor.SK_Unit_ID AND tractor.ETL_Current_Row = 1
LEFT JOIN hr.vw_Dim_ICC_Driver driver1
ON fd.SK_Driver1_ID = driver1.SK_Driver_ID
LEFT JOIN DispatchStops
ON CONCAT( Order_Num, '-', Dispatch_Num ) = DispatchStops.DispatchKey
LEFT JOIN ( SELECT Order_Num, SUM( Total_Miles ) OrderTotalMiles, SUM( Empty_Miles ) OrderEmptyMiles, MAX( Dispatch_Num ) finaldispatch FROM ops.tbl_Fact_ICC_Dispatch GROUP BY Order_Num ) AS finalDispatch
ON fd.order_num = finaldispatch.order_num
WHERE
DATEDIFF( DAY, Dispatch_Datetime, CURRENT_TIMESTAMP ) <= 121
AND driver1.Driver_Code IS NOT NULL-- AND driver2.driver_code IS NULL
AND tractor.Division IN( '300', '200' )
--AND driver1.Driver_Code = 'MEDWI'
--ORDER BY DriverCode1, Dispatch_Datetime
), CircuitIndexer AS(
SELECT
Division,
DriverCode1,
DriverHome,
circuitstarter,
circuitend,
ROW_NUMBER() OVER( PARTITION BY DriverCode1 ORDER BY CircuitStarter ) CircuitIndex
FROM(
SELECT
Division,
DriverCode1,
DriverHome,
CASE WHEN circuitstart IS NULL THEN LAG( CircuitStart, 1 ) OVER( PARTITION BY DriverCode1 ORDER BY Dispatch_Datetime ) END circuitstarter,
circuitend
FROM FactDisp
WHERE circuitstart IS NOT NULL OR circuitend IS NOT NULL ) AS CircuitTimestamps
), CircuitsFactDisp AS(
SELECT
FactDisp.Division,
FactDisp.DriverCode1,
DispatchKey,
CircuitIndex,
ROW_NUMBER() OVER( PARTITION BY FactDisp.DriverCode1, CircuitIndex ORDER BY Dispatch_Datetime ) ordersequence,
CASE
WHEN RoutelinePickup IS NULL THEN LAG( RoutelineDrop, 1 ) OVER( PARTITION BY FactDisp.DriverCode1 ORDER BY Dispatch_Datetime )
ELSE RoutelinePickup
END RoutelinePickup,
CASE
WHEN RoutelineDrop IS NULL THEN LEAD( RoutelinePickup, 1 ) OVER( PARTITION BY FactDisp.DriverCode1 ORDER BY Dispatch_Datetime )
WHEN RoutelineDrop IS NULL AND TotalMiles <= 66 AND RoutelinePickup IS NOT NULL THEN RoutelinePickup
WHEN RoutelineDrop IS NULL AND TotalMiles <= 66 AND RoutelinePickup IS NULL THEN LAG( RoutelineDrop, 1 ) OVER( PARTITION BY FactDisp.DriverCode1 ORDER BY Dispatch_Datetime )
ELSE RoutelineDrop
END RoutelineDrop,
CASE WHEN TotalMiles IS NULL THEN 0 ELSE TotalMiles END TotalMiles,
Dispatch_Datetime,
Dispatch_End_Datetime
FROM FactDisp
LEFT JOIN CircuitIndexer
ON FactDisp.DriverCode1 = CircuitIndexer.DriverCode1 AND FactDisp.Dispatch_Datetime >= CircuitIndexer.circuitstarter AND FactDisp.Dispatch_End_Datetime <= CircuitIndexer.circuitend
), FactDispCircuitPivot AS(
SELECT
Division, DriverCode1, CircuitIndex,
MIN( Dispatch_Datetime ) CircuitStartCTS, MAX( Dispatch_End_Datetime ) CircuitEndCTS,
DATEDIFF( HOUR, MIN( Dispatch_Datetime ), MAX( Dispatch_End_Datetime )) CircuitDuration,
CONCAT_WS( ' / ', MAX( [1] ), MAX( [2] ), MAX( [3] ), MAX( [4] ), MAX( [5] ), MAX( [6] ), MAX( [7] ), MAX( [8] )) CircuitRoute,
MAX( [1] ) disp1, MAX( [2] ) disp2, MAX( [3] ) disp3, MAX( [4] ) disp4,
MAX( [5] ) disp5, MAX( [6] ) disp6, MAX( [7] ) disp7, MAX( [8] ) disp8,
COALESCE( MAX( [11] ), 0 ) + COALESCE( MAX( [12] ), 0 ) + COALESCE( MAX( [13] ), 0 ) + COALESCE( MAX( [14] ), 0 ) +
COALESCE( MAX( [15] ), 0 ) + COALESCE( MAX( [16] ), 0 ) + COALESCE( MAX( [17] ), 0 ) + COALESCE( MAX( [18] ), 0 ) CircuitMilesSum,
MAX( [11] ) Miles1, MAX( [12] ) Miles2, MAX( [13] ) Miles3, MAX( [14] ) Miles4,
MAX( [15] ) Miles5, MAX( [16] ) Miles6, MAX( [17] ) Miles7, MAX( [18] ) Miles8
FROM(
SELECT
Division, DriverCode1, CircuitIndex, oseq,
CONVERT( DATETIME, Dispatch_Datetime ) AT TIME ZONE 'Mountain Standard Time' AT TIME ZONE 'Mountain Standard Time' Dispatch_Datetime,
CONVERT( DATETIME, Dispatch_End_Datetime ) AT TIME ZONE 'Mountain Standard Time'AT TIME ZONE 'Mountain Standard Time' Dispatch_End_Datetime,
[1], [2], [3], [4], [5], [6], [7], [8], [11], [12], [13], [14], [15], [16], [17], [18]
FROM
(
SELECT
Division, DriverCode1, CircuitIndex,
CONCAT( routelinepickup, '-', routelinedrop ) routeline, TotalMiles, Dispatch_Datetime, Dispatch_End_Datetime, DATEDIFF( HOUR, Dispatch_Datetime, Dispatch_End_Datetime ) dispdur,
ordersequence, ordersequence oseq,
ordersequence +10 oseq1
FROM CircuitsFactDisp
WHERE CircuitIndex IS NOT NULL
) AS pivotter
PIVOT(
MAX( Routeline ) FOR OrderSequence IN( [1], [2], [3], [4], [5], [6], [7], [8] )
) AS pivotteralso
PIVOT(
SUM( TotalMiles ) FOR oseq1 IN( [11], [12], [13], [14], [15], [16], [17], [18] )) AS maxxertable
) AS pivotter2
GROUP BY Division, DriverCode1, CircuitIndex
), unIpivit AS(
SELECT
Division, DriverCode1, CircuitIndex, RIGHT( OrderSequeunce, 1 ) ordersequence, Routeline, CircuitRoute, CircuitMilesSum,
LEFT( Routeline, 3 ) RoutelinePickup, RIGHT( Routeline, 3 ) RoutelineDrop--, TotalMiles
FROM(
SELECT * FROM FactDispCircuitPivot
) as Z
UNPIVOT( Routeline FOR OrderSequeunce IN( [disp1], [disp2], [disp3], [disp4], [disp5], [disp6] )
) AS unpi
), Fuel AS(
SELECT
CONCAT_WS( '-', Order_Num, Dispatch_Num ) dskey,
Station_ID_Code, Station_Name,
MAX( Station_City ) City, MAX( State ) State, SUM( Quantity ) quant
FROM ( SELECT * FROM ops.vw_Purchased_Fuel WHERE DATEDIFF( DAY, Transaction_Datetime, CURRENT_TIMESTAMP ) < 133 ) AS Z
GROUP BY CONCAT_WS( '-', Order_Num, Dispatch_Num ), Station_ID_Code, Station_Name
)
SELECT *
FROM(
SELECT
z.Division, z.DriverCode1, z.CircuitIndex, z.ordersequence, Routeline, CircuitRoute, CircuitMilesSum,
TotalMiles, Dispatch_Datetime, DispatchKey
FROM unIpivit z
LEFT JOIN CircuitsFactDisp y
ON z.DriverCode1 = y.DriverCode1 AND z.CircuitIndex = y.CircuitIndex AND z.Routeline = concat( y.RoutelinePickup, '-', y.RoutelineDrop )
WHERE CircuitRoute IN(
'CO1-NM3 / NM3-ELP / ELP-CA7 / CA7-CO1',
'NM3-ELP / ELP-CA7 / CA4-CO1 / CO1-NM3',
'ELP-CA7 / CA4-CO1 / CO1-NM3 / NM3-ELP',
'ELP-CA4 / CA4-CO1 / CO1-ELP',
'ELP-TX2 / TX2-CO1 / CO1-NM3 / NM3-ELP',
'ELP-TX2 / TX2-CO1 / CO1-CO1 / CO1-NM3 / NM3-ELP',
'ELP-TX2 / TX2-CO1 / CO1-ELP'
)) AS z
LEFT JOIN Fuel y
ON z.DispatchKey = y.dskey
ORDER BY Division, CircuitRoute, DriverCode1, CircuitIndex, ordersequence
