PT BIA

??--CREATE PROCEDURE [dbo].[PDSTMTOPYVALEMPRUNEXIST]

--(

--	@Variant VARCHAR(20),

--	@StartDate VARCHAR(20),

--	@EndDate VARCHAR(20),

--	@EmployeeIDStart VARCHAR(20),

--	@EmployeeIDEnd VARCHAR(20)

--)

--AS

DECLARE @EmployeeIDStart VARCHAR(20) = '06120011'

DECLARE @EmployeeIDEnd VARCHAR(20) = '06120011'

DECLARE @StartDate VARCHAR(20) = '20221001'

DECLARE @EndDate VARCHAR(20) = '20221031'

DECLARE @Variant VARCHAR(20) = '0001'

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 = '') 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