【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: