PT BIA

--CREATE PROCEDURE 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)

	,CPORett DECIMAL(22,2)

	,CPO2Rett DECIMAL(22,2)

	,CPO3Rett DECIMAL(22,2)

)

INSERT INTO @CTARIF

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

PIVOT (MAX(Tarif)

FOR FieldName IN

(	[CPORett],

	[CPO2Rett],

	[CPO3Rett])

) AS pvt

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

				,md.CPORett * tarif.CPORett AS TarifCPORett

				,md.CPO2Rett * tarif.CPO2Rett AS TarifCPO2Rett

				,md.CPO3Rett * tarif.CPO3Rett 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)