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