Project

General

Profile

Bug #3749 » PRPTTMABSENCEPHOTO_202507281438.sql

Tri Rizqiaty, 07/28/2025 03:47 PM

 
1
??ALTER PROCEDURE [dbo].[PRPTTMABSENCEPHOTO]
2

3
(
4

5
	@EmployeeID VARCHAR (20),
6

7
	----@Period VARCHAR (6),	--// komen by Tri 20250728
8

9
	@CompanyID VARCHAR(20)
10

11
	,@StartDate VARCHAR(20)		--// add by Tri 20250728
12

13
	,@EndDate VARCHAR(20)		--// add by Tri 20250728
14

15
)
16

17
AS
18

19

20

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

23
----DECLARE @Period VARCHAR (6)='202505'		--// komen by Tri 20250728
24

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

27
--DECLARE @StartDate VARCHAR(20) = '20240101'	--// add by Tri 20250728
28

29
--DECLARE @EndDate VARCHAR(20) = '20250801'		--// add by Tri 20250728
30

31

32

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

35

36

37
select 
38

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

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

43
,pa19.EmployeeID
44

45
,pa1.FullName
46

47
,att.AttendanceTypeDesc as AttendanceType
48

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

51
,pa19.StartLocation
52

53
,pa68.FileDataClockIn
54

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

57
,pa19.EndLocation
58

59
,pa68.FileDataClockOut
60

61
,pa19.AuditorFlag 
62

63
from PHRPA0019  pa19
64

65
left join PHRPA0068 pa68 on pa19.AuditorFlag=pa68.id and pa68.EmployeeID = pa19.EmployeeID
66

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

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

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

73
where
74

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

77
----and left(pa19.StartDate,6)=@Period							--// komen by Tri 20250728
78

79
AND pa19.StartDate >= @StartDate AND pa19.StartDate <= @EndDate --// add by Tri 20250728
80

81
AND (pa2.CompanyID = @CompanyID OR @CompanyID = '')
82

83
order by pa19.StartDate,pa19.EmployeeID
84

85

(2-2/4)