PT BIA

??ALTER PROCEDURE [dbo].[PDSTMTOPYVALEMPRUNEXIST]

(

	@Variant VARCHAR(20),

	@StartDate VARCHAR(20),

	@EndDate VARCHAR(20),

	@EmployeeIDStart VARCHAR(20),

	@EmployeeIDEnd VARCHAR(20)

)

AS

--DECLARE @EmployeeIDStart VARCHAR(20) = '11100027'

--DECLARE @EmployeeIDEnd VARCHAR(20) = '11100027'

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

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

--DECLARE @Variant VARCHAR(20) = '0002'

DECLARE @EmpIDMin VARCHAR(20) = CASE WHEN @EmployeeIDStart <> '' THEN @EmployeeIDStart ELSE '00000000' END

DECLARE @EmpIDMax VARCHAR(20) = CASE WHEN @EmployeeIDEnd <> '' THEN @EmployeeIDEnd ELSE '999999999' END

DECLARE @FlagPYTM VARCHAR(20)

DECLARE @Flag VARCHAR(20)

DECLARE @EmpIDRun VARCHAR(250)

SELECT TOP(1) @EmpIDRun = (tr800.EmployeeID + '-' + pa01.FullName)

			, @FlagPYTM = (CASE WHEN (tr800.Payroll = '1' AND (tr800.TM = '' OR tr800.TM IS NULL)) THEN 'PY' 

							WHEN (tr800.Payroll = '' AND tr800.TM <> '') THEN 'TM' 

							WHEN (tr800.Payroll = '1' AND tr800.TM <> '') THEN 'PYTM' 

						END)

FROM dbo.PHRPYTR0800 AS tr800

LEFT JOIN dbo.PHRPA0001 AS pa01

	ON pa01.EmployeeID = tr800.EmployeeID AND pa01.StartDate <= @EndDate AND pa01.EndDate >= @EndDate

WHERE (@StartDate BETWEEN tr800.StartDate AND tr800.EndDate)

AND tr800.Payroll = '1'

AND (tr800.EmployeeID BETWEEN @EmpIDMin AND @EmpIDMax)

IF( @FlagPYTM <> '')

BEGIN 

	SELECT ISNULL(@EmpIDRun,'') AS EmpIDRun, @FlagPYTM AS Flag

END

ELSE

BEGIN

	SELECT TOP(1) @EmpIDRun = ( tr800.EmployeeID + '-' + pa01.FullName )

	FROM dbo.PHRPYTR0800 AS tr800

	LEFT JOIN dbo.PHRPA0001 AS pa01

		ON pa01.EmployeeID = tr800.EmployeeID AND pa01.StartDate <= @EndDate AND pa01.EndDate >= @EndDate

	WHERE (@StartDate BETWEEN tr800.StartDate AND tr800.EndDate) 

		AND (tr800.EmployeeID BETWEEN @EmpIDMin AND @EmpIDMax)

		AND (Variant = @Variant OR @Variant = '')

	ORDER BY tr800.EmployeeID

	SET @Flag = (CASE WHEN @EmpIDRun <> '' THEN 'Y' ELSE 'N' END)

	SELECT ISNULL(@EmpIDRun,'') AS EmpIDRun, @Flag AS Flag

END