Project

General

Profile

Download (4.12 KB)

Support #1910 » CBIATMTOPYPERAWATANTARIF_20230109.sql

Tri Rizqiaty, 01/09/2023 04:16 PM

 
1 
??ALTER PROCEDURE [dbo].[CBIATMTOPYPERAWATANTARIF]
2
3 
(
4
5 
	@EmployeeID VARCHAR(20)
6
7 
	,@StartDate VARCHAR(20)
8
9 
	,@EndDate VARCHAR(20)
10
11 
)
12
13 
AS
14
15
16
17 
--DECLARE @EmployeeID VARCHAR(20) = '20010835'
18
19 
--DECLARE @StartDate VARCHAR(20) = '20230101'
20
21 
--DECLARE @EndDate VARCHAR(20) = '20230131'
22
23
24
25 
DECLARE @CTARIF TABLE
26
27 
(
28
29 
	EmployeeID VARCHAR(20)
30
31 
    ,[KodeKegiatan] VARCHAR(20)
32
33 
    ,[Tarif21] VARCHAR(20)
34
35 
    ,[TargetKerjaPerHari] VARCHAR(20)
36
37 
    ,[TargetKerjaHariSabtu] VARCHAR(20)
38
39 
)
40
41
42
43 
INSERT INTO @CTARIF
44
45 
SELECT @EmployeeID
46
47 
	,[KodeKegiatan]
48
49 
	,[Tarif21]
50
51 
	,[TargetKerjaPerHari]
52
53 
	,[TargetKerjaHariSabtu]
54
55 
FROM CTARIFPERAWATAN
56
57
58
59 
SELECT DISTINCT md.EmployeeID
60
61 
				,md.StartDate
62
63 
				,md.HasilKerja1
64
65 
				,md.HasilKerja2
66
67 
				,md.HasilKerja3
68
69 
				,md.HasilKerja4
70
71 
				,md.HasilKerja5
72
73 
				,md.TotalHasilKerja
74
75 
				,md.LebihTarget
76
77 
				,md.KurangTarget
78
79 
				,CASE WHEN CONVERT(DECIMAL(22,2),md.LebihTarget) > 0 THEN CONVERT(DECIMAL(22,2),md.LebihTarget)
80
81 
					  WHEN CONVERT(DECIMAL(22,2),md.KurangTarget) > 0 THEN CONVERT(DECIMAL(22,2),md.KurangTarget) * -1
82
83 
					  ELSE 0
84
85 
				 END SelisihTarget
86
87 
				,tarif.Tarif21 AS TarifPerawatan21
88
89 
				,upah.UpahPokok AS UpahCapaiTarget
90
91 
				,upah.PremiBasis AS PremiCapaiTarget
92
93 
				,CONVERT(DECIMAL(22,0),
94
95 
				 CASE WHEN CONVERT(DECIMAL(22,2),md.LebihTarget) > 0 THEN CONVERT(DECIMAL(22,2),upah.UpahPokok) + CONVERT(DECIMAL(22,2),upah.PremiBasis) + (CONVERT(DECIMAL(22,2),md.LebihTarget) * CONVERT(DECIMAL(22,2),tarif.Tarif21))
96
97 
					  WHEN CONVERT(DECIMAL(22,2),md.KurangTarget) > 0 THEN CONVERT(DECIMAL(22,2),upah.UpahPokok) - (CONVERT(DECIMAL(22,2),md.KurangTarget) * CONVERT(DECIMAL(22,2),tarif.Tarif21))
98
99 
					  ELSE CONVERT(DECIMAL(22,2),upah.UpahPokok) + CONVERT(DECIMAL(22,2),upah.PremiBasis)
100
101 
				 END) AS TotalPerawatan
102
103 
FROM dbo.CUSPYDATAPREMIPERAWATAN  AS md
104
105 
LEFT JOIN @CTARIF AS tarif
106
107 
	ON md.EmployeeID = tarif.EmployeeID
108
109 
LEFT JOIN CTARIFBRDPANEN AS upah
110
111 
	ON upah.StartDate <= @EndDate AND upah.EndDate >= @EndDate
112
113 
WHERE  md.EmployeeID = @EmployeeID
114
115 
	AND (md.StartDate BETWEEN @StartDate AND @EndDate)
116
117 
	AND (md.KodeKegiatan = tarif.KodeKegiatan)
118
119
(3-3/4)