PT BIA

ALTER PROCEDURE CBIATMTOPYHINOTARIF

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

)

AS

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

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

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

DECLARE @CTARIF TABLE

(

	EmployeeID VARCHAR(20)

	,QtyTrip1 DECIMAL(22,0)

	,QtyTrip2 DECIMAL(22,0)

	,QtyTrip3 DECIMAL(22,0)

)

INSERT INTO @CTARIF

SELECT @EmployeeID, * FROM (SELECT FieldName, Tarif FROM CTARIFHINO) AS SourceTable

PIVOT (MAX(Tarif)

FOR FieldName IN

(	[QtyTrip1],

	[QtyTrip2],

	[QtyTrip3])

) AS pvt

SELECT DISTINCT md.EmployeeID, md.QtyTrip1,  md.QtyTrip2,  md.QtyTrip3 

				,md.QtyTrip1 * tarif.QtyTrip1 AS TarifQtyTrip1

				,md.QtyTrip2 * tarif.QtyTrip2 AS TarifQtyTrip2

				,md.QtyTrip3 * tarif.QtyTrip3 AS TarifQtyTrip3

FROM dbo.CUSPYDATAPREMIHINO AS md 

LEFT JOIN @CTARIF AS tarif

	ON md.EmployeeID = tarif.EmployeeID

WHERE  md.EmployeeID = @EmployeeID

	AND (md.StartDate BETWEEN @StartDate AND @EndDate)