PT BIA

??--USE [MinovaES_BIA_Dev]

--GO

--/****** Object:  StoredProcedure [dbo].[CBIATMTOPYTRIPHINOTARIF]    Script Date: 24/01/2023 11.50.06 ******/

--SET ANSI_NULLS ON

--GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CBIATMTOPYTRIPHINOTARIF]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

)

AS

--DECLARE @EmployeeID VARCHAR(20) = '14060239'

--DECLARE @StartDate VARCHAR(20) = '20230101'

--DECLARE @EndDate VARCHAR(20) = '20230131'

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

DECLARE @TblResult TABLE

(

	EmployeeID VARCHAR(20)

	,TipeTrip01 VARCHAR(20)

	,TipeTrip02 VARCHAR(20)

	,TipeTrip03 VARCHAR(20)

	,TipeTrip04 VARCHAR(20)

	,TipeTrip05 VARCHAR(20)

	,TipeTrip06 VARCHAR(20)

	,TipeTrip07 VARCHAR(20)

	,TipeTrip08 VARCHAR(20)

	,TipeTrip09 VARCHAR(20)

	,TipeTrip10 VARCHAR(20)

	,TipeTrip11 VARCHAR(20)

	,TipeTrip12 VARCHAR(20)

	,TipeTrip13 VARCHAR(20)

	,TipeTrip14 VARCHAR(20)

	,TipeTrip15 VARCHAR(20)

	,TipeTrip16 VARCHAR(20)

	,TipeTrip17 VARCHAR(20)

	,TipeTrip18 VARCHAR(20)

	,TipeTrip19 VARCHAR(20)

	,TarifTipeTrip01 DECIMAL(22,2)

	,TarifTipeTrip02 DECIMAL(22,2)

	,TarifTipeTrip03 DECIMAL(22,2)

	,TarifTipeTrip04 DECIMAL(22,2)

	,TarifTipeTrip05 DECIMAL(22,2)

	,TarifTipeTrip06 DECIMAL(22,2)

	,TarifTipeTrip07 DECIMAL(22,2)

	,TarifTipeTrip08 DECIMAL(22,2)

	,TarifTipeTrip09 DECIMAL(22,2)

	,TarifTipeTrip10 DECIMAL(22,2)

	,TarifTipeTrip11 DECIMAL(22,2)

	,TarifTipeTrip12 DECIMAL(22,2)

	,TarifTipeTrip13 DECIMAL(22,2)

	,TarifTipeTrip14 DECIMAL(22,2)

	,TarifTipeTrip15 DECIMAL(22,2)

	,TarifTipeTrip16 DECIMAL(22,2)

	,TarifTipeTrip17 DECIMAL(22,2)

	,TarifTipeTrip18 DECIMAL(22,2)

	,TarifTipeTrip19 DECIMAL(22,2)

	,TotalTarifTripHino DECIMAL(22,2)

)

INSERT INTO @TblResult

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