Skip to Main Content
  • Questions
  • Calculate more than attendance period per day with some conditions


Question and Answer

Chris Saxon

Thanks for the question, ahmed .

Asked: May 23, 2023 - 1:26 pm UTC

Last updated: May 26, 2023 - 8:30 am UTC

Version: 22.2

Viewed 1000+ times

You Asked

I have the following query:
select OPERATION_CODE, to_char(OPERATION_DATE, 'dd/mm/yyyy hh24:mi:ss') as OPERATION_DATE, EMP_CODE
where EMP_CODE = 4415
and to_char(OPERATION_DATE, 'yyyymmdd') = 20230517

And I have the following output:
1 17/05/2023 07:08:03 4415
1 17/05/2023 07:55:15 4415
2 17/05/2023 08:00:00 4415
1 17/05/2023 15:07:01 4415
2 17/05/2023 16:00:00 4415
2 17/05/2023 16:58:27 4415
2 17/05/2023 17:26:05 4415
1 17/05/2023 20:00:00 4415

The report query I want is as follow:
Entrance Leave
17/05/2023 07:08:03 17/05/2023 08:00:00
17/05/2023 15:07:01 17/05/2023 17:26:05
17/05/2023 20:00:00

Witch meaning that, the first attendance period row is the min entrance and the max leave before the next entrance period start. Where the leave can be overrided by the user, but if the user entrance more than one time without leave, I want to select the min entrance. I hope it is clear.
Thank you.

The above was my question in an old thread, and Chris answer me with the following:
select * from hr_original_attendance
match_recognize (
order by operation_date
first ( op1.operation_date ) st_dt,
last ( op2.operation_date ) en_dt
pattern ( op1+ op2+ )
op1 as operation_code = first ( operation_code ),
op2 as operation_code <> first ( operation_code )

The result was:
-------- --------
17/05/23 17/05/23
17/05/23 17/05/23

And Connor follow up, and answer me the following:
prep (operation_code, operation_date, emp_code) as (
select operation_code, operation_date, emp_code
from hr_original_attendance
where emp_code = 4415 and operation_date >= date '2023-05-17'
and operation_date < date '2023-05-18'
select emp_code, entrance, leave
from prep
order by operation_date
measures first(emp_code) as emp_code,
first(one.operation_date) as entrance,
last (two.operation_date) as leave
pattern ( one* two* )
define one as operation_code = 1, two as operation_code = 2

The result was:
---------- ------------------- -------------------
4415 17/05/2023 07:08:03 17/05/2023 08:00:00
4415 17/05/2023 15:07:01 17/05/2023 17:26:05
4415 17/05/2023 20:00:00

the exactly was I need.

Thank you very much Chris and Connor.

The new question is that: When I have oracle 11gr2 version, how to rewrite the query?
Thank you.

and Connor said...

Easy ... upgrade your database!

11g2 was release in 2009...... same as the iphone 3

I'm pretty sure no-one is using an iphone 3 anymore

If you really want to head down this path, check out


  (1 rating)


SQL solution: start-of-group method

mathguy, May 25, 2023 - 6:10 pm UTC

I don't think that follow-up was from Connor.

Anyway - if you must do this in Oracle 11.2, you can't use MATCH_RECOGNIZE. Please remember for yourself, and tell ten more people: ALWAYS, when you ask a question like you did, include your Oracle version!

Here is how you can do the same in SQL, using the start-of-group method. I agree with Connor that ideally you should upgrade your Oracle version. I also know that in practice there are even very large organizations in the U.S. that have not (as of now) upgraded from 11.2, for whatever reason. And the start-of-group method may be helpful in some problems even in newer versions of Oracle, so it's worth keeping it in the arsenal. Alas, the link Connor provided is to a different problem, which doesn't actually have a natural "pure SQL" solution in 11.2 (there are some hacks, but they are ugly and very inefficient). Your problem does.

I assume the EMP_CODE and the report date are given, as in your problem description (and I have them hard-coded, you can change to bind variables or procedure parameters or whatever you need; I don't illustrate that below). If you need to do this for all employees at the same time, you will need to modify the analytic functions to add PARTITION BY clause.

  prep as (
    select operation_code, operation_date, emp_code,
           case when operation_code < lag(operation_code) over (order by operation_date)
                then 1 end as flag
    from   hr_original_attendance
    where  emp_code = 4415 and operation_date >= date '2023-05-17'
                           and operation_date <  date '2023-05-18'
, add_groups as (
    select operation_code, operation_date, emp_code,
           count(flag) over (order by operation_date) as grp
    from   prep
select emp_code,
       min(case operation_code when 1 then operation_date end) as entrance,
       max(case operation_code when 2 then operation_date end) as leave
from   add_groups
group  by grp, emp_code
order  by entrance nulls first

Chris Saxon
May 26, 2023 - 8:30 am UTC

I don't think the follow up was from Connor either ;)

Thanks for sharing

More to Explore


Analytic SQL got you confused? Check out Connor McDonald's complete video course.