• Questions
• # Calculate more than attendance period per day with some conditions

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

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

And I have the following output:
OPERATION_CODE OPERATION_DATE EMP_CODE
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
measures
first ( op1.operation_date ) st_dt,
last ( op2.operation_date ) en_dt
pattern ( op1+ op2+ )
define
op1 as operation_code = first ( operation_code ),
op2 as operation_code <> first ( operation_code )
);

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

with
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
match_recognize(
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:
EMP_CODE ENTRANCE LEAVE
---------- ------------------- -------------------
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...

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

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

## Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

### 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.

```with
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'
)
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
group  by grp, emp_code
order  by entrance nulls first
;```

May 26, 2023 - 8:30 am UTC

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

Thanks for sharing

# More to Explore

##### Analytics

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