CREATE PROCEDURE [dbo].[CBIATMTOPYTRIPHINOTARIF]
(
	@EmployeeID VARCHAR(20)
	,@StartDate VARCHAR(20)
	,@EndDate VARCHAR(20)
)
AS

--DECLARE @EmployeeID VARCHAR(20) = '06120011'
--DECLARE @StartDate VARCHAR(20) = '20221101'
--DECLARE @EndDate VARCHAR(20) = '20221130'

DECLARE @CTARIF TABLE
(
	EmployeeID VARCHAR(20)
	,TipeTrip01 DECIMAL(22,2)
	,TipeTrip02 DECIMAL(22,2)
	,TipeTrip03 DECIMAL(22,2)
	,TipeTrip04 DECIMAL(22,2)
	,TipeTrip05 DECIMAL(22,2)
	,TipeTrip06 DECIMAL(22,2)
	,TipeTrip07 DECIMAL(22,2)
	,TipeTrip08 DECIMAL(22,2)
	,TipeTrip09 DECIMAL(22,2)
	,TipeTrip10 DECIMAL(22,2)
	,TipeTrip11 DECIMAL(22,2)
	,TipeTrip12 DECIMAL(22,2)
	,TipeTrip13 DECIMAL(22,2)
	,TipeTrip14 DECIMAL(22,2)
	,TipeTrip15 DECIMAL(22,2)
	,TipeTrip16 DECIMAL(22,2)
	,TipeTrip17 DECIMAL(22,2)
	,TipeTrip18 DECIMAL(22,2)
	,TipeTrip19 DECIMAL(22,2)
)

INSERT INTO @CTARIF
SELECT @EmployeeID, * FROM (SELECT FieldName, Tarif AS Tarif FROM CTARIFTRIPHINODUMPTRUCK) AS SourceTable
PIVOT (MAX(Tarif)
FOR FieldName IN
(	[TipeTrip01],
	[TipeTrip02],
	[TipeTrip03],
	[TipeTrip04],
	[TipeTrip05],
	[TipeTrip06],
	[TipeTrip07],
	[TipeTrip08],
	[TipeTrip09],
	[TipeTrip10],
	[TipeTrip11],
	[TipeTrip12],
	[TipeTrip13],
	[TipeTrip14],
	[TipeTrip15],
	[TipeTrip16],
	[TipeTrip17],
	[TipeTrip18],
	[TipeTrip19])
) AS pvt

SELECT DISTINCT md.EmployeeID, md.TipeTrip01,  md.TipeTrip02,  md.TipeTrip03, md.TipeTrip04, md.TipeTrip05, md.TipeTrip06
				,md.TipeTrip07 ,md.TipeTrip08 ,md.TipeTrip09 ,md.TipeTrip10 ,md.TipeTrip11 ,md.TipeTrip12 ,md.TipeTrip13
				,md.TipeTrip14 ,md.TipeTrip15 ,md.TipeTrip16 ,md.TipeTrip17 ,md.TipeTrip18 ,md.TipeTrip19
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip01 <> '' THEN md.TipeTrip01 ELSE '0' END) * tarif.TipeTrip01 AS TarifTipeTrip01
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip02 <> '' THEN md.TipeTrip02 ELSE '0' END) * tarif.TipeTrip02 AS TarifTipeTrip02
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip03 <> '' THEN md.TipeTrip03 ELSE '0' END) * tarif.TipeTrip03 AS TarifTipeTrip03
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip04 <> '' THEN md.TipeTrip04 ELSE '0' END) * tarif.TipeTrip04 AS TarifTipeTrip04
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip05 <> '' THEN md.TipeTrip05 ELSE '0' END) * tarif.TipeTrip05 AS TarifTipeTrip05
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip06 <> '' THEN md.TipeTrip06 ELSE '0' END) * tarif.TipeTrip06 AS TarifTipeTrip06
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip07 <> '' THEN md.TipeTrip07 ELSE '0' END) * tarif.TipeTrip07 AS TarifTipeTrip07
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip08 <> '' THEN md.TipeTrip08 ELSE '0' END) * tarif.TipeTrip08 AS TarifTipeTrip08
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip09 <> '' THEN md.TipeTrip09 ELSE '0' END) * tarif.TipeTrip09 AS TarifTipeTrip09
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip10 <> '' THEN md.TipeTrip10 ELSE '0' END) * tarif.TipeTrip10 AS TarifTipeTrip10
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip11 <> '' THEN md.TipeTrip11 ELSE '0' END) * tarif.TipeTrip11 AS TarifTipeTrip11
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip12 <> '' THEN md.TipeTrip12 ELSE '0' END) * tarif.TipeTrip12 AS TarifTipeTrip12
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip13 <> '' THEN md.TipeTrip13 ELSE '0' END) * tarif.TipeTrip13 AS TarifTipeTrip13
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip14 <> '' THEN md.TipeTrip14 ELSE '0' END) * tarif.TipeTrip14 AS TarifTipeTrip14
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip15 <> '' THEN md.TipeTrip15 ELSE '0' END) * tarif.TipeTrip15 AS TarifTipeTrip15
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip16 <> '' THEN md.TipeTrip16 ELSE '0' END) * tarif.TipeTrip16 AS TarifTipeTrip16
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip17 <> '' THEN md.TipeTrip17 ELSE '0' END) * tarif.TipeTrip17 AS TarifTipeTrip17
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip18 <> '' THEN md.TipeTrip18 ELSE '0' END) * tarif.TipeTrip18 AS TarifTipeTrip18
				,CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip19 <> '' THEN md.TipeTrip19 ELSE '0' END) * tarif.TipeTrip19 AS TarifTipeTrip19
				,(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip01 <> '' THEN md.TipeTrip01 ELSE '0' END) * tarif.TipeTrip01) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip02 <> '' THEN md.TipeTrip02 ELSE '0' END) * tarif.TipeTrip02) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip03 <> '' THEN md.TipeTrip03 ELSE '0' END) * tarif.TipeTrip03) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip04 <> '' THEN md.TipeTrip04 ELSE '0' END) * tarif.TipeTrip04) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip05 <> '' THEN md.TipeTrip05 ELSE '0' END) * tarif.TipeTrip05) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip06 <> '' THEN md.TipeTrip06 ELSE '0' END) * tarif.TipeTrip06) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip07 <> '' THEN md.TipeTrip07 ELSE '0' END) * tarif.TipeTrip07) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip08 <> '' THEN md.TipeTrip08 ELSE '0' END) * tarif.TipeTrip08) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip09 <> '' THEN md.TipeTrip09 ELSE '0' END) * tarif.TipeTrip09) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip10 <> '' THEN md.TipeTrip10 ELSE '0' END) * tarif.TipeTrip10) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip11 <> '' THEN md.TipeTrip11 ELSE '0' END) * tarif.TipeTrip11) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip12 <> '' THEN md.TipeTrip12 ELSE '0' END) * tarif.TipeTrip12) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip13 <> '' THEN md.TipeTrip13 ELSE '0' END) * tarif.TipeTrip13) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip14 <> '' THEN md.TipeTrip14 ELSE '0' END) * tarif.TipeTrip14) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip15 <> '' THEN md.TipeTrip15 ELSE '0' END) * tarif.TipeTrip15) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip16 <> '' THEN md.TipeTrip16 ELSE '0' END) * tarif.TipeTrip16) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip17 <> '' THEN md.TipeTrip17 ELSE '0' END) * tarif.TipeTrip17) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip18 <> '' THEN md.TipeTrip18 ELSE '0' END) * tarif.TipeTrip18) +
				(CONVERT(DECIMAL(22,2),CASE WHEN md.TipeTrip19 <> '' THEN md.TipeTrip19 ELSE '0' END) * tarif.TipeTrip19) AS TotalTarifTripHino
FROM dbo.MDTRIPHINODUMPTRUCK  AS md 
LEFT JOIN @CTARIF AS tarif
	ON md.EmployeeID = tarif.EmployeeID
WHERE  md.EmployeeID = @EmployeeID
	AND (md.StartDate BETWEEN @StartDate AND @EndDate)