Project

General

Profile

Download (6.1 KB)

Support #1819 » CBIATMTOPYPINALTITARIF.sql

Tri Rizqiaty, 11/30/2022 05:07 PM

 
1 
??CREATE PROCEDURE [dbo].[CBIATMTOPYPINALTITARIF]
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) = '06120011'
18
19 
--DECLARE @StartDate VARCHAR(20) = '20221101'
20
21 
--DECLARE @EndDate VARCHAR(20) = '20221130'
22
23
24
25 
DECLARE @CTARIF TABLE
26
27 
(
28
29 
	EmployeeID VARCHAR(20)
30
31 
	,TipePinalti01 DECIMAL(22,2)
32
33 
	,TipePinalti02 DECIMAL(22,2)
34
35 
	,TipePinalti03 DECIMAL(22,2)
36
37 
	,TipePinalti04 DECIMAL(22,2)
38
39 
	,TipePinalti05 DECIMAL(22,2)
40
41 
	,TipePinalti06 DECIMAL(22,2)
42
43 
	,TipePinalti07 DECIMAL(22,2)
44
45 
	,TipePinalti08 DECIMAL(22,2)
46
47 
	,TipePinalti09 DECIMAL(22,2)
48
49 
	,TipePinalti10 DECIMAL(22,2)
50
51 
	,TipePinalti11 DECIMAL(22,2)
52
53 
	,TipePinalti12 DECIMAL(22,2)
54
55 
	,TipePinalti13 DECIMAL(22,2)
56
57 
)
58
59
60
61 
INSERT INTO @CTARIF
62
63 
SELECT @EmployeeID, * FROM (SELECT FieldName, Tarif FROM CTARIFPINALTI) AS SourceTable
64
65 
PIVOT (MAX(Tarif)
66
67 
FOR FieldName IN
68
69 
(	[TipePinalti01],
70
71 
	[TipePinalti02],
72
73 
	[TipePinalti03],
74
75 
	[TipePinalti04],
76
77 
	[TipePinalti05],
78
79 
	[TipePinalti06],
80
81 
	[TipePinalti07],
82
83 
	[TipePinalti08],
84
85 
	[TipePinalti09],
86
87 
	[TipePinalti10],
88
89 
	[TipePinalti11],
90
91 
	[TipePinalti12],
92
93 
	[TipePinalti13])
94
95 
) AS pvt
96
97
98
99 
SELECT DISTINCT md.EmployeeID, md.TipePinalti01,  md.TipePinalti02,  md.TipePinalti03, md.TipePinalti04, md.TipePinalti05, md.TipePinalti06
100
101 
				,md.TipePinalti07 ,md.TipePinalti08 ,md.TipePinalti09 ,md.TipePinalti10 ,md.TipePinalti11 ,md.TipePinalti12 ,md.TipePinalti13
102
103 
				,md.TipePinalti01 * tarif.TipePinalti01 AS TarifTipePinalti01
104
105 
				,md.TipePinalti02 * tarif.TipePinalti02 AS TarifTipePinalti02
106
107 
				,md.TipePinalti03 * tarif.TipePinalti03 AS TarifTipePinalti03
108
109 
				,md.TipePinalti04 * tarif.TipePinalti04 AS TarifTipePinalti04
110
111 
				,md.TipePinalti05 * tarif.TipePinalti05 AS TarifTipePinalti05
112
113 
				,md.TipePinalti06 * tarif.TipePinalti06 AS TarifTipePinalti06
114
115 
				,md.TipePinalti07 * tarif.TipePinalti07 AS TarifTipePinalti07
116
117 
				,md.TipePinalti08 * tarif.TipePinalti08 AS TarifTipePinalti08
118
119 
				,md.TipePinalti09 * tarif.TipePinalti09 AS TarifTipePinalti09
120
121 
				,md.TipePinalti10 * tarif.TipePinalti10 AS TarifTipePinalti10
122
123 
				,md.TipePinalti11 * tarif.TipePinalti11 AS TarifTipePinalti11
124
125 
				,md.TipePinalti12 * tarif.TipePinalti12 AS TarifTipePinalti12
126
127 
				,md.TipePinalti13 * tarif.TipePinalti13 AS TarifTipePinalti13
128
129 
				,(md.TipePinalti01 * tarif.TipePinalti01) +
130
131 
				(md.TipePinalti02 * tarif.TipePinalti02) +
132
133 
				(md.TipePinalti03 * tarif.TipePinalti03) +
134
135 
				(md.TipePinalti04 * tarif.TipePinalti04) +
136
137 
				(md.TipePinalti05 * tarif.TipePinalti05) +
138
139 
				(md.TipePinalti06 * tarif.TipePinalti06) +
140
141 
				(md.TipePinalti07 * tarif.TipePinalti07) +
142
143 
				(md.TipePinalti08 * tarif.TipePinalti08) +
144
145 
				(md.TipePinalti09 * tarif.TipePinalti09) +
146
147 
				(md.TipePinalti10 * tarif.TipePinalti10) +
148
149 
				(md.TipePinalti11 * tarif.TipePinalti11) +
150
151 
				(md.TipePinalti12 * tarif.TipePinalti12) +
152
153 
				(md.TipePinalti13 * tarif.TipePinalti13) AS TotalTarifPinalti
154
155 
FROM dbo.CUSPYDATAPREMIDENDA  AS md
156
157 
LEFT JOIN @CTARIF AS tarif
158
159 
	ON md.EmployeeID = tarif.EmployeeID
160
161 
WHERE  md.EmployeeID = @EmployeeID
162
163 
	AND (md.StartDate BETWEEN @StartDate AND @EndDate)
(5-5/8)