ALTER PROCEDURE [dbo].[CBIATMTOPYPERAWATANTARIF]
(
	@EmployeeID VARCHAR(20)
	,@StartDate VARCHAR(20)
	,@EndDate VARCHAR(20)
)
AS

--DECLARE @EmployeeID VARCHAR(20) = '20010835'
--DECLARE @StartDate VARCHAR(20) = '20230101'
--DECLARE @EndDate VARCHAR(20) = '20230131'

DECLARE @CTARIF TABLE
(
	EmployeeID VARCHAR(20)
    ,[KodeKegiatan] VARCHAR(20)
    ,[Tarif21] VARCHAR(20)
    ,[TargetKerjaPerHari] VARCHAR(20)
    ,[TargetKerjaHariSabtu] VARCHAR(20)
)

INSERT INTO @CTARIF
SELECT @EmployeeID
	,[KodeKegiatan]
	,[Tarif21]
	,[TargetKerjaPerHari]
	,[TargetKerjaHariSabtu] 
FROM CTARIFPERAWATAN

SELECT DISTINCT md.EmployeeID
				,md.StartDate
				,md.HasilKerja1
				,md.HasilKerja2
				,md.HasilKerja3
				,md.HasilKerja4
				,md.HasilKerja5
				,md.TotalHasilKerja
				,md.LebihTarget
				,md.KurangTarget
				,CASE WHEN CONVERT(DECIMAL(22,2),md.LebihTarget) > 0 THEN CONVERT(DECIMAL(22,2),md.LebihTarget) 
					  WHEN CONVERT(DECIMAL(22,2),md.KurangTarget) > 0 THEN CONVERT(DECIMAL(22,2),md.KurangTarget) * -1
					  ELSE 0 
				 END SelisihTarget
				,tarif.Tarif21 AS TarifPerawatan21
				,upah.UpahPokok AS UpahCapaiTarget
				,upah.PremiBasis AS PremiCapaiTarget
				,CONVERT(DECIMAL(22,0),
				 CASE WHEN CONVERT(DECIMAL(22,2),md.LebihTarget) > 0 THEN CONVERT(DECIMAL(22,2),upah.UpahPokok) + CONVERT(DECIMAL(22,2),upah.PremiBasis) + (CONVERT(DECIMAL(22,2),md.LebihTarget) * CONVERT(DECIMAL(22,2),tarif.Tarif21))
					  WHEN CONVERT(DECIMAL(22,2),md.KurangTarget) > 0 THEN CONVERT(DECIMAL(22,2),upah.UpahPokok) - (CONVERT(DECIMAL(22,2),md.KurangTarget) * CONVERT(DECIMAL(22,2),tarif.Tarif21))
					  ELSE CONVERT(DECIMAL(22,2),upah.UpahPokok) + CONVERT(DECIMAL(22,2),upah.PremiBasis)
				 END) AS TotalPerawatan
FROM dbo.CUSPYDATAPREMIPERAWATAN  AS md 
LEFT JOIN @CTARIF AS tarif
	ON md.EmployeeID = tarif.EmployeeID
LEFT JOIN CTARIFBRDPANEN AS upah
	ON upah.StartDate <= @EndDate AND upah.EndDate >= @EndDate
WHERE  md.EmployeeID = @EmployeeID
	AND (md.StartDate BETWEEN @StartDate AND @EndDate)
	AND (md.KodeKegiatan = tarif.KodeKegiatan)