PT BIA

??--USE [MinovaES_BIA_Dev]

--GO

--/****** Object:  StoredProcedure [dbo].[CBIATMTOPYHINOTARIF]    Script Date: 24/01/2023 11.16.34 ******/

--SET ANSI_NULLS ON

--GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CBIATMTOPYHINOTARIF]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

)

AS

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

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

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

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

DECLARE @TblResult TABLE

(

	EmployeeID VARCHAR(20)

	,QtyTrip1 VARCHAR(20)

	,QtyTrip2 VARCHAR(20)

	,QtyTrip3 VARCHAR(20)

	,TarifQtyTrip1 DECIMAL(22,0)

	,TarifQtyTrip2 DECIMAL(22,0)

	,TarifQtyTrip3 DECIMAL(22,0)

)

INSERT INTO @TblResult

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)

SELECT DISTINCT EmployeeID

				,SUM(TarifQtyTrip1) AS TarifQtyTrip1

				,SUM(TarifQtyTrip2) AS TarifQtyTrip2

				,SUM(TarifQtyTrip3) AS TarifQtyTrip3

FROM @TblResult

GROUP BY EmployeeID