EKNO YUAG

??ALTER PROCEDURE [dbo].[PRPTTMABSENCEPHOTO]

(

	@EmployeeID VARCHAR (20),

	@Period VARCHAR (6),

	@CompanyID VARCHAR(20)

)

AS

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

--DECLARE @Period VARCHAR (6)='202505'

--DECLARE @CompanyID VARCHAR(20)  = '1004'

DECLARE @EndDate VARCHAR(20) = dbo.fn_formatdatetime(DATEADD(ms, -3, dateadd(mm, datediff(mm, 0, (@Period+'01')) + 1, 0)), 'yyyymmdd')

select 

dbo.fn_formatdatetime(pa19.StartDate , 'dd MMMM yyyy') as  StartDate

,dbo.fn_formatdatetime(pa19.EndDate , 'dd MMMM yyyy') as  EndDate

,pa19.EmployeeID

,pa1.FullName

,att.AttendanceTypeDesc as AttendanceType

,left(pa19.StartTime,2)+':'+right(pa19.StartTime,2) as StartTime

,pa19.StartLocation

,pa68.FileDataClockIn

,left(pa19.EndTime,2)+':'+right(pa19.EndTime,2) as EndTime

,pa19.EndLocation

,pa68.FileDataClockOut

,pa19.AuditorFlag 

from PHRPA0019  pa19

left join PHRPA0068 pa68 on pa19.AuditorFlag=pa68.id

left join PHRTMATTTYP att on pa19.AttendanceType=att.AttendanceType and att.EndDate='99991231'

left join PHRPA0001 pa1 on pa1.EmployeeID=pa19.EmployeeID and pa1.EndDate='99991231'

LEFT JOIN dbo.PHRPA0002 pa2 ON pa2.EmployeeID = pa1.EmployeeID AND pa2.StartDate <= @EndDate AND pa2.EndDate >= @EndDate

where

(pa19.EmployeeID=@EmployeeID or @EmployeeID='')

and left(pa19.StartDate,6)=@Period

--AND (pa2.CompanyID = @CompanyID OR @CompanyID = '')

order by pa19.StartDate,pa19.EmployeeID