--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