Get Eligible Records from Each Group
【Question】
Attendance table:
EmpName EmpID ClockInTime
Kerry 20513 2014-6-27 08:40:45
Kerry 20513 2014-6-27 17:50:47
Kerry 20513 2014-6-28 8:14:54
Kerry 20513 2014-6-28 8:19:54
Kerry 20513 2014-6-28 18:14:54
Sam 11304 2014-06-27 16:40:50
Sam 11304 2014-06-27 17:40:50
For each employee, I want to get their attendance record. The clock-in time is from 7 o’clock to 13 o’clock. The clock-out time begins from 16 o’clock. If an employee has multiple clock-in or clock-out records, get the earliest as clock-in time and the latest as clock-out time. If an employee doesn’t have any records during the stated time interval, they are recorded as not clock-in or not clock-out. It is regarded as late if an employee clocks in after 8:30 and as leave work early if they clock out before 18:00.
Blow is the expected output:
Date EmpName ClockIn ClockOut IfLate IfLeaveEarly
2014-6-27 Kerry 2014-6-27 08:40:45 2014-6-27 18:20:47 Late LeaveEarly
2014-6-27 Sam NotClockIn 2014-06-27 17:40:50 NotClockIn LeaveEarly
2014-6-28 Kerry 2014-6-28 8:14:54 2014-6-28 18:14:54
【Answer】
The logic is simple. But it involves a lot of judges. It’s difficult to write all judges in one SQL query. You’d better write them in a step-by-step way with a stored procedure. The thing is that it’s not convenient to get the first and the last records. It seems that the query is used to build an attendance report for HR or Financial department. I think you can try doing it with RaqReport. The reporting tool encapsulates a special computing engine (esProc SPL) to prepare the report data source and conveniently generate a script in a stepwise format.
Here’s the SPL (Structured Process Language) script:
A |
B |
C |
|
1 |
$select * from Attendance order by EmpID, ClockInTime |
||
2 |
=A1.group(EmpName,date(ClockInTime)) |
||
3 |
=create(Date,EmpDate,ClockIn,ClockOut,IfLate,IfLeaveEarly) |
||
4 |
for A2 |
=A4(1).ClockInTime |
=string(time(B4)) |
5 |
=A4.m(-1).ClockInTime |
=string(time(B5)) |
|
6 |
=if(C4>="07:00:00" && C4<="12:00:00",B4,"NotClockIn") |
||
7 |
=if(C5>="16:00:00",B5,"NotClockIn") |
||
8 |
=if(C4>="08:30:00" && C4<="12:00:00"," Late ", if(C4<="08:30:00","","NotClockIn")) |
||
9 |
=if(C5>="16:00:00"&& C5<="18:00:00"," LeaveEarly ", if(C5>="18:00:00","","NotClockIn")) |
||
10 |
>A3.insert(0,date(A4.ClockInTime),A4.EmpName,B6,B7,B8,B9) |
||
11 |
result A3 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL