PT BIA

??ALTER PROCEDURE [dbo].[CBIATMTOPYCPOTARIF]

(

	@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)

	,TipeCPO VARCHAR(20)

	,PKS01 DECIMAL(22,2)

	,PKS02 DECIMAL(22,2)

	,PKS03 DECIMAL(22,2)

)

INSERT INTO @CTARIF

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

PIVOT (MAX(Tarif)

FOR FieldName IN

(	[PKS01],

	[PKS02],

	[PKS03])

) AS pvt

SELECT DISTINCT md.EmployeeID, md.CPORett,  md.CPO2Rett,  md.CPO3Rett 

				,md.PKS1Tersus * tarif.PKS01 AS TarifCPORett

				,md.PKS2Tersus * tarif.PKS02 AS TarifCPO2Rett

				,md.PKS3Tersus * tarif.PKS03 AS TarifCPO3Rett

FROM dbo.CUSPYDATACPO  AS md 

LEFT JOIN @CTARIF AS tarif

	ON md.EmployeeID = tarif.EmployeeID

WHERE  md.EmployeeID = @EmployeeID

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

	AND (md.NoUnit = TipeCPO)