Personal Folder

Typical users using employee in out absent report:

1. Employee, see accurate minute he entry to and leave from plant, check whether he is late/early home or not. Absent data is one of criterias employee deserve for incentive from the company.
2. HR Dept, count employee pay work hour and employee overtime.
3. HSE Dept, count safety work hour and overtime. Superintendent level and above usually not report their existence on plant on overtime hour since their not get pay. But HSE want their data to increase safety work hour.

With that user requirement, the data must contain:

• employee id
• employee daily/shift schedule on subsequent date
• employee’s real in out absent time (and date)
• pay work hour
• safety work hour
• pay overtime
• safety overtime

Note that on daily type employee consist of Daily Merak (07:30 – 16:30) and Daily Jakarta (08:00-17:00). And for shift employee, there are 3 shift (morning shift, afternoon and night). Each shift has 4 group, group A, B, C and D. Night shift have special process, since leave time is smaller than entry time. Below are complete schedule of employee:

Type	Kode	JamDatang	JamPulang	Keterangan
Daily Merak	DM	0730	1630	Normal Jam Kerja Merak
Daily Jakarta	DJ	0800	1700	Normal Jam Kerja Jakarta
Shift Pagi	M0	0600	1400	Technisi
Shift Siang	A0	1400	2200	Technisi
Shift Malam	N0	2200	0600	Technisi
Shift Spv Pagi	M1	0700	1500	Shift Supervisor,Warehouse Assistant
Shift Spv Siang	A1	1500	2300	Shift Supervisor,Warehouse Assistant
Shift Spv Malam	N1	2300	0700	Shift Supervisor,Warehouse Assistant
AC Pagi	M4	0600	1500	Technisi AC
AC Siang	A4	1000	1900	Technisi AC
Driver Pagi	M5	0600	1500	Driver
Driver Siang	A5	1500	2400	Driver
12 Jam Pagi	M2	0600	1800	12 Jam 06:00 - 18:00
12 Jam Malam	N2	1800	0600	12 Jam 18:00 - 06:00
12 Jam Spv & Security Pagi	M3	0700	1900	12 Jam 07:00 - 19:00
12 Jam Spv & Security Malam	N3	1900	0700	12 Jam 19:00 - 07:00
Rest/Libur	R0	0	0	Libur Shift
Weighbridge Opr Pagi	W0	0700	1500	Weighbridge Operator
Weighbridge Opr Siang	W1	1500	2300	Weighbridge Operator
Security Pagi	S0	0700	1500	Security Pagi
Security Siang	S1	1500	2300	Security Siang
Security Malam	S2	2300	0700	Security Malam
Driver Pola A Pagi	MD0	0700	1500	Driver Shift Pola A Pagi
Driver Pola A Siang	AD0	1500	2300	Driver Shift Pola A Siang
Driver Pola A Malam	ND0	2300	0700	Driver Pola A Pagi
Driver Pola B Pagi	MD1	0600	1400	Driver Pola B Pagi
Driver Pola B Siang	AD1	1400	2200	Driver Pola B Siang
Driver Daily	DD0	0730	1630	Driver Daily

Here is the result:

Many table and view contribute to produce the result above, and below are the steps:

Process on ESOFNET Database

• Trans_R > detail of employee in out, consist of card number, time, code whether employee is entering or leaving the plant.
• vTrans_R_v2 > Trans_R left join with Cards table so that the view has staff number and employee daily shift code.

SELECT dbo.Trans_R.Tr_Data, ISNULL(dbo.Cards.Staff_Number, '') AS staff_number, dbo.Trans_R.Tr_Date, dbo.Trans_R.Tr_Time, dbo.Trans_R.Tr_Code
FROM dbo.Trans_R LEFT OUTER JOIN
dbo.Cards ON dbo.Trans_R.Tr_Data = dbo.Cards.Card_Number
WHERE (dbo.Cards.Staff_Number <> '')

• vTrans_R_v2_step1 > vTrans_R_v2 with min in and max out of time as RealDatang dan RealPulang, grouped by entry/leave code.

SELECT Tr_Data, staff_number, Tr_Date, MIN(Tr_Time) AS RealDatang, MAX(Tr_Time) AS RealPulang, Tr_Code
FROM dbo.vTrans_R_v2
GROUP BY Tr_Data, staff_number, Tr_Date, Tr_Code

• vTrans_R_v2_step2 > normalizing vTrans_R_v2_step1 so it don’t contain redundant double data, and also switch in out data and put into new column (needed for night shift data that leave time is smaller than entry time).

SELECT Tr_Data, staff_number, Tr_Date, MIN(RealDatang) AS RealDatang, MAX(RealPulang) AS RealPulang, MAX(RealPulang) AS RealDatangN,
MIN(RealDatang) AS RealPulangN
FROM dbo.vTrans_R_v2_step1
GROUP BY Tr_Data, staff_number, Tr_Date

• vTrans_R_v2_step3 > vTrans_R_v2_step2 with safety work minute calculation. Also contain switching process for night shift.

SELECT Tr_Data, staff_number, Tr_Date, RealDatang, CAST(SUBSTRING(RealDatang, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealDatang, 3, 2) AS int)
AS RealDatangMinute, RealPulang, CAST(SUBSTRING(RealPulang, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealPulang, 3, 2) AS int) AS RealPulangMinute,
RealDatangN, CAST(SUBSTRING(RealDatangN, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealDatangN, 3, 2) AS int) AS RealDatangMinuteN, RealPulangN,
CAST(SUBSTRING(RealPulangN, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealPulangN, 3, 2) AS int) AS RealPulangMinuteN,
(CAST(SUBSTRING(RealPulang, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealPulang, 3, 2) AS int)) - (CAST(SUBSTRING(RealDatang, 1, 2) AS int)
* 60 + CAST(SUBSTRING(RealDatang, 3, 2) AS int)) AS WorkMinuteHSE, (CAST(SUBSTRING(RealPulangN, 1, 2) AS int)
* 60 + CAST(SUBSTRING(RealPulangN, 3, 2) AS int) + 1440) - (CAST(SUBSTRING(RealDatangN, 1, 2) AS int) * 60 + CAST(SUBSTRING(RealDatangN, 3, 2)
AS int)) AS WorkMinuteHSEN
FROM dbo.vTrans_R_v2_step2

• vTrans_R_v2_step4 > vTrans_R_v2_step3 with safety work hour calculation.

SELECT Tr_Data, staff_number, Tr_Date, RealDatang, RealDatangMinute, RealPulang, RealPulangMinute, RealDatangN, RealDatangMinuteN, RealPulangN,
RealPulangMinuteN, WorkMinuteHSE, WorkMinuteHSEN, RIGHT('00' + CAST(CAST(WorkMinuteHSE / 60 AS int) AS nvarchar), 2)
+ RIGHT('00' + CAST(WorkMinuteHSE % 60 AS nvarchar), 2) AS WorkHourHSE, RIGHT('00' + CAST(CAST(WorkMinuteHSEN / 60 AS int) AS nvarchar), 2)
+ RIGHT('00' + CAST(WorkMinuteHSEN % 60 AS nvarchar), 2) AS WorkHourHSEN
FROM dbo.vTrans_R_v2_step3

Process at EMPLOYEE Database

• Table_JamDailyShift > time schedule based on schedule code.
• Table_EmpTimeSheet2 > time schedule per employee.
• vTable_EmpTimeSheet2_v2 > Table_EmpTimeSheet2 joined Table_EmpTimeSheet2, to count pay work minute.

SELECT dbo.Table_EmpTimeSheet2.empid, dbo.Table_EmpTimeSheet2.tr_date, dbo.Table_EmpTimeSheet2.sched_code,
ISNULL(dbo.Table_JamDailyShift.JamDatang, 0) AS JamDatang, ISNULL(dbo.Table_JamDailyShift.JamPulang, 0) AS JamPulang,
CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 1, 2) AS int) * 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 3, 2) AS int)
AS JamPulangMinute, (CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 1, 2) AS int)
* 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 3, 2) AS int)) - (CAST(SUBSTRING(dbo.Table_JamDailyShift.JamDatang, 1, 2) AS int)
* 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamDatang, 3, 2) AS int)) AS MenitKerja, (CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 1,
2) AS int) * 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamPulang, 3, 2) AS int) + 1440) - (CAST(SUBSTRING(dbo.Table_JamDailyShift.JamDatang,
1, 2) AS int) * 60 + CAST(SUBSTRING(dbo.Table_JamDailyShift.JamDatang, 3, 2) AS int)) AS MenitKerjaN
FROM dbo.Table_EmpTimeSheet2 LEFT OUTER JOIN
dbo.Table_JamDailyShift ON dbo.Table_EmpTimeSheet2.sched_code = dbo.Table_JamDailyShift.Kode

• vTable_EmpTimeSheet2_v2_step1 > from vTable_EmpTimeSheet2_v2, pay minute of daily type employee has to subtract by 60 since he have an hour rest time (shift employee don’t have rest time).

SELECT empid, tr_date, sched_code, JamDatang, JamPulang, JamPulangMinute, CASE upper(substring(sched_code, 1, 1))
WHEN 'N' THEN MenitKerjaN ELSE (CASE upper(substring(sched_code, 1, 1)) WHEN 'D' THEN MenitKerja - 60 ELSE MenitKerja END)
END AS MenitKerja
FROM dbo.vTable_EmpTimeSheet2_v2

• vAbsensi_step1 > left join Table_EmpTimeSheet2 (scheduled in out) with vTrans_R_v2_step4 (real in out). This view has time switch process for night shift employee, and processing on safety overtime minute.

SELECT dbo.vTable_EmpTimeSheet2_v2_step1.empid, dbo.vTable_EmpTimeSheet2_v2_step1.tr_date, dbo.vTable_EmpTimeSheet2_v2_step1.sched_code,
dbo.vTable_EmpTimeSheet2_v2_step1.JamDatang, dbo.vTable_EmpTimeSheet2_v2_step1.JamPulang,
dbo.vTable_EmpTimeSheet2_v2_step1.JamPulangMinute, dbo.vTable_EmpTimeSheet2_v2_step1.MenitKerja, CASE upper(substring(sched_code, 1, 1))
WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealPulang, 0) ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealDatang, 0)
END AS RealDatang, CASE upper(substring(sched_code, 1, 1)) WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealDatang, 0)
ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealPulang, 0) END AS RealPulang, CASE upper(substring(sched_code, 1, 1))
WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealDatangMinute, 0) ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.RealPulangMinute, 0)
END AS RealPulangMinute, CASE upper(substring(sched_code, 1, 1)) WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.WorkMinuteHSEN, 0)
ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.WorkMinuteHSE, 0) END AS WorkMinuteHSE, CASE upper(substring(sched_code, 1, 1))
WHEN 'N' THEN isnull(ESOFNET.dbo.vTrans_R_v2_step4.WorkHourHSEN, 0) ELSE isnull(ESOFNET.dbo.vTrans_R_v2_step4.WorkHourHSE, 0)
END AS WorkHourHSE,
ESOFNET.dbo.vTrans_R_v2_step4.WorkMinuteHSE - dbo.vTable_EmpTimeSheet2_v2_step1.MenitKerja AS OvertimeMinuteHSE
FROM dbo.vTable_EmpTimeSheet2_v2_step1 LEFT OUTER JOIN
ESOFNET.dbo.vTrans_R_v2_step4 ON dbo.vTable_EmpTimeSheet2_v2_step1.empid = ESOFNET.dbo.vTrans_R_v2_step4.staff_number AND
dbo.vTable_EmpTimeSheet2_v2_step1.tr_date = ESOFNET.dbo.vTrans_R_v2_step4.Tr_Date

• vAbsensi_step2 > left join vAbsensi_step2 with Table_Employee. Also has calculation on safety overtime hour and pay overtime minute.

SELECT dbo.vAbsensi_step1.empid, dbo.vAbsensi_step1.tr_date, dbo.vAbsensi_step1.sched_code, dbo.vAbsensi_step1.JamDatang,
dbo.vAbsensi_step1.JamPulang, dbo.vAbsensi_step1.JamPulangMinute, dbo.vAbsensi_step1.MenitKerja, dbo.vAbsensi_step1.RealDatang,
dbo.vAbsensi_step1.RealPulang, dbo.vAbsensi_step1.RealPulangMinute, dbo.vAbsensi_step1.WorkMinuteHSE, CASE upper(substring(sched_code, 1,
1)) WHEN 'D' THEN dbo.vAbsensi_step1.WorkMinuteHSE - 60 ELSE dbo.vAbsensi_step1.WorkMinuteHSE END AS PayWorkMinute,
dbo.vAbsensi_step1.WorkHourHSE, dbo.vAbsensi_step1.OvertimeMinuteHSE,
RIGHT('00' + CAST(CAST(dbo.vAbsensi_step1.OvertimeMinuteHSE / 60 AS int) AS nvarchar), 2)
+ RIGHT('00' + CAST(dbo.vAbsensi_step1.OvertimeMinuteHSE % 60 AS nvarchar), 2) AS OvertimeHourHSE,
dbo.vAbsensi_step1.RealPulangMinute - dbo.vAbsensi_step1.JamPulangMinute AS PayOvertimeMinute
FROM dbo.vAbsensi_step1 LEFT OUTER JOIN
dbo.Table_Employee ON dbo.vAbsensi_step1.empid = dbo.Table_Employee.EmpId

• vAbsensi_step3 > process vAbsensi_step2, convert pay work minute to pay work hour.

SELECT empid, tr_date, sched_code, JamDatang, JamPulang, MenitKerja, RealDatang, RealPulang, WorkMinuteHSE, PayWorkMinute, WorkHourHSE,
RIGHT('00' + CAST(CAST(PayWorkMinute / 60 AS int) AS nvarchar), 2) + RIGHT('00' + CAST(PayWorkMinute % 60 AS nvarchar), 2) AS PayWorkHour,
OvertimeMinuteHSE, OvertimeHourHSE, CASE substring(sched_code, 1, 1)
WHEN 'R' THEN PayWorkMinute WHEN 'H' THEN PayWorkMinute ELSE PayOvertimeMinute END AS PayOvertimeMinute
FROM dbo.vAbsensi_step2

• vAbsensi_step4 > process vAbsensi_step3, convert pay overtime minute to pay overtime hour.

SELECT empid, tr_date, sched_code, JamDatang, JamPulang, MenitKerja, RealDatang, RealPulang, WorkMinuteHSE, PayWorkMinute, WorkHourHSE,
PayWorkHour, OvertimeMinuteHSE, OvertimeHourHSE, PayOvertimeMinute, RIGHT('00' + CAST(CAST(PayOvertimeMinute / 60 AS int) AS nvarchar), 2)
+ RIGHT('00' + CAST(PayOvertimeMinute % 60 AS nvarchar), 2) AS PayOvertimeHour
FROM dbo.vAbsensi_step3


vAbsensi_step4 is the final process producing the result.