Support #759
[ESS-LEAVE] Create Query untuk Transfer Data dari Table PCMWFRUNBIZDATA ke PHRPA0018
Description
Mang Wanto,
Mohon bantuannya buatin query untuk transfer data dari Table PCMWFRUNBIZDATA ke PHRPA0018 di:
Db: remote.minovais.com,1442\SQLSERVER2014
Database: MinovaES_BBG_Dev
Contoh RegID : 5594
Tabel: PCMWFRUNMAIN, PCMWFRUNBIZDATA, PHRPA0018
detail informasi yang perlu dikerjakan terlampir di file ppt di bawah ini ya mang. Terimakasih.
Salam,
Ika
Files
Updated by ikawati basri about 3 years ago
Sorry mang,
lokasi Db nya di remote.minovais.com,1438\SQLSERVER2012
Updated by Saswanto Tampan about 3 years ago
- Status changed from New to QA Test
- Assignee changed from Saswanto Tampan to ikawati basri
DECLARE @PHRPA0018 AS TABLE
(
[NUMBER] VARCHAR,
RegID VARCHAR,
[StartDate] VARCHAR,
[EndDate] VARCHAR,
[EmployeeID] VARCHAR,
[AbsenceType] VARCHAR,
[IDOnBehalf] VARCHAR,
[AbsenceAddress] VARCHAR,
[AbsencePhone] VARCHAR,
[Notes] VARCHAR,
[CreateBy] VARCHAR,
[CreateDate] VARCHAR,
[ChangeBy] VARCHAR,
[ChangeDate] VARCHAR,
[TotalAbsenceTaken] VARCHAR
)
SELECT DISTINCT RegID,MAXAS SEQ, FieldID,Value,BizProcessID INTO #TEMP FROM PCMWFRUNBIZDATA WHERE BizProcessID='Leave_AJTM' AND
(FieldID='DelegateTo' OR FieldID='AbsenceType' OR FieldID='StartDate'OR FieldID='EndDate'OR FieldID='TotalAbsenceTaken'OR (FieldID='LeaveConfirmation'AND Value='y'))
AND RegID IN (SELECT RegID FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING = '2021' )
GROUP BY RegID,FieldID,Value,BizProcessID
DECLARE @ROWSNUMBER INT = (SELECT COUNT FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING = '2021')
DECLARE @i int = 1
WHILE @i <= @ROWSNUMBER
BEGIN
INSERT INTO @PHRPA0018 VALUES ( @i ,'','','','','','','','','','','','','','')
SET @i = @i + 1
END
UPDATE @PHRPA0018 SET
RegID =A.RegID FROM (SELECT ROW_NUMBER() OVERAS NUMBER,RegID FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING = '2021' GROUP BY RegID)A
WHERE A.NUMBER = [@PHRPA0018].NUMBER
UPDATE @PHRPA0018 SET
StartDate =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='StartDate')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID
UPDATE @PHRPA0018 SET
EndDate =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='EndDate')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID
UPDATE @PHRPA0018 SET
EmployeeID =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='DelegateTo')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID
UPDATE @PHRPA0018 SET
AbsenceType =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='AbsenceType')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID
UPDATE @PHRPA0018 SET
TotalAbsenceTaken =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='TotalAbsenceTaken')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID
INSERT INTO PHRPA0018
SELECT
[StartDate]
,[EndDate]
,[EmployeeID]
,[AbsenceType]
,[IDOnBehalf]
,[AbsenceAddress]
,[AbsencePhone]
,[Notes]
,[CreateBy]
,[CreateDate]
,[ChangeBy]
,[ChangeDate]
,[TotalAbsenceTaken]
FROM @PHRPA0018
DROP TABLE #TEMP
Updated by ikawati basri about 3 years ago
Mang Wanto,
Maaf ya ada update filter data untuk tarik data dari PCMWFRUNBIZDATA ke PHRPA0018, karena dengan filter yang dipake kemarin ada data yang seharusnya terhitung sebagai cuti, tapi tidak masuk ke PHRPA0018.
Perubahannya adalah, filter leave confirmation = "y" tidak digunakan, digantikan dengan filter data di field "FlowType=400" di table PCMWFRUNMAIN.
Terlampir catatannya yaa.
Db: remote.minovais.com,1438\SQLSERVER2012
Database: MinovaES_BBG_Dev
Contoh RegID : 5594, 55495, 55496
Tabel: PCMWFRUNMAIN, PCMWFRUNBIZDATA, PHRPA0018
Salam,
Ika