Skip to Main Content
  • Questions
  • Count number of occurances by date and by code.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Venkat.

Asked: December 13, 2016 - 4:01 pm UTC

Last updated: May 23, 2023 - 4:55 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi All-

I need to create a report that shows number of occurrences of a student being suspended (by 2 different suspension codes) for a range of dates (usually first day of month and last day of month) by school.

Here is the starting query, that I use to come up with the data set.
SELECT s.student_number,      
       s.grade_level,
       att.schoolid "SCH",
       att.att_date,
       to_char(att.att_date, 'DAY') day , 
       ac.att_code
FROM  students s, attendance att, attendance_code ac
WHERE s.id = att.studentid AND
      att.ATTENDANCE_CODEID = ac.ID AND
      ac.att_code in ('S','X') AND
      att.ATT_MODE_CODE = 'ATT_ModeDaily' and
      att.SCHOOLID in (82) and
      att.ATT_DATE between TO_DATE('11/01/2016','MM/DD/YYYY') and TO_DATE('11/30/2016','MM/DD/YYYY')
order by 6,1,4 


The part of output is like so:

181535  10 82 11/03/2016  S 1
181535  10 82 11/04/2016  S 
181535  10 82 11/07/2016  S 
181535  10 82 11/16/2016  S 1
181535  10 82 11/17/2016  S 
181535  10 82 11/18/2016  S 


The last column are the number of occurrences. Consecutive dates are to be considered as one occurrence, irrespective of a week end
and or a school holiday. I do have the school calendar in a separate table.

What would be the best way that this can be produced?

Thanks!

=============

In continuation, here are the create statements, sample data and expected results. thank!

CREATE TABLE STUDENTS
(
  ID                           NUMBER(10)     NOT NULL,
  schoolid                       number(10),
   STUDENT_NUMBER                 NUMBER(10)
)
/

insert into students values(1821,82,181535) ;
insert into students values(13892,82,180784);
insert into students values(12877,82,161314);
insert into students values(14088,82,181728);
/

CREATE TABLE ATTENDANCE
(
  ID                   NUMBER(10) NOT NULL,
  CALENDAR_DAYID       NUMBER(10) ,
  ATTENDANCE_CODEID    NUMBER(10),  
  STUDENTID            NUMBER(10),  
  schoolid             number(10),
  ATT_MODE_CODE        VARCHAR2(20 CHAR),  
  ATT_DATE             DATE  
)
/ 
 insert into attendance values ( 23642330,254090,10126,1821,82,'ATT_ModeDaily',03-NOV-16);
 insert into attendance values ( 23642335,254091,10126,1821,82,'ATT_ModeDaily',04-NOV-16);
 insert into attendance values ( 23676557,254094,10126,1821,82,'ATT_ModeDaily',07-NOV-16);
 insert into attendance values ( 23726471,254103,10126,1821,82,'ATT_ModeDaily',16-NOV-16);
 insert into attendance values ( 23726457,254104,10126,1821,82,'ATT_ModeDaily',17-NOV-16);
 insert into attendance values ( 23726461,254105,10126,1821,82,'ATT_ModeDaily',18-NOV-16); 
 insert into attendance values ( 23707597,254102,10126,13892,82,'ATT_ModeDaily',15-NOV-16);
 insert into attendance values ( 23710920,254103,10126,13892,82,'ATT_ModeDaily',16-NOV-16);
 insert into attendance values ( 23726967,254104,10126,13892,82,'ATT_ModeDaily',17-NOV-16);
 insert into attendance values ( 23726965,254105,10126,13892,82,'ATT_ModeDaily',18-NOV-16);
 insert into attendance values ( 23766755,254108,10126,13892,82,'ATT_ModeDaily',21-NOV-16);
 insert into attendance values ( 23766780,254109,10126,13892,82,'ATT_ModeDaily',22-NOV-16);
 insert into attendance values ( 23766753,254110,10126,13892,82,'ATT_ModeDaily',23-NOV-16);
 insert into attendance values ( 23810950,254115,10126,13892,82,'ATT_ModeDaily',28-NOV-16);
 insert into attendance values ( 23829495,254116,10126,13892,82,'ATT_ModeDaily',29-NOV-16);
 insert into attendance values ( 23759842,254105,10130,12877,82,'ATT_ModeDaily',18-NOV-16);
 insert into attendance values ( 23818901,254115,10130,12877,82,'ATT_ModeDaily',28-NOV-16); 
 insert into attendance values ( 23670409,254094,10130,14088,82,'ATT_ModeDaily',07-NOV-16);
 insert into attendance values ( 23693514,254096,10130,14088,82,'ATT_ModeDaily',09-NOV-16);
 /

 CREATE TABLE ATTENDANCE_CODE
( ID                       NUMBER(10) NOT NULL,
  SCHOOLID                 NUMBER(10), 
  ATT_CODE                 VARCHAR2(10 CHAR)  
)
/
INSERT INTO ATTENDANCE_CODE VALUES (10126,82,'S');
INSERT INTO ATTENDANCE_CODE VALUES (10130,82,'X');
/

--- CALENDAR TABLE LOOK UP --- CODE 1 IS WORKING 0 IS NONWORKING

CREATE TABLE CALENDAR_DAY
( ID                NUMBER(10) NOT NULL,
  SCHOOLID          NUMBER(10),
  DATE_VALUE        DATE,
  INSESSION         NUMBER(10)
)  
/
254088,82,01-NOV-16,1;
254089,82,02-NOV-16,1;
254090,82,03-NOV-16,1;
254091,82,04-NOV-16,1;
254092,82,05-NOV-16,0;
254093,82,06-NOV-16,0;
254094,82,07-NOV-16,1;
254095,82,08-NOV-16,0;
254096,82,09-NOV-16,1;
254097,82,10-NOV-16,0;
254098,82,11-NOV-16,0;
254099,82,12-NOV-16,0;
254100,82,13-NOV-16,0;
254101,82,14-NOV-16,1;
254102,82,15-NOV-16,1;
254103,82,16-NOV-16,1;
254104,82,17-NOV-16,1;
254105,82,18-NOV-16,1;
254106,82,19-NOV-16,0;
254107,82,20-NOV-16,0;
254108,82,21-NOV-16,1;
254109,82,22-NOV-16,1;
254110,82,23-NOV-16,1;
254111,82,24-NOV-16,0;
254112,82,25-NOV-16,0;
254113,82,26-NOV-16,0;
254114,82,27-NOV-16,0;
254115,82,28-NOV-16,1;
254116,82,29-NOV-16,1;
254117,82,30-NOV-16,1;
/

STU # GRADE SCH # DATE CODE OCCURRENCE
180784 10 82 11/15/2016  S 1
180784 10 82 11/16/2016  S 
180784 10 82 11/17/2016  S 
180784 10 82 11/18/2016  S 
180784 10 82 11/21/2016  S 
180784 10 82 11/22/2016  S 
180784 10 82 11/23/2016  S 
180784 10 82 11/28/2016  S 
180784 10 82 11/29/2016  S 
     
181535 10 82 11/03/2016  S 1
181535 10 82 11/04/2016  S 
181535 10 82 11/07/2016  S 
181535 10 82 11/16/2016  S 1
181535 10 82 11/17/2016  S 
181535 10 82 11/18/2016  S 
     
161314 9 82 11/18/2016  X 1
161314 9 82 11/28/2016  X 1
     
181728 9 82 11/07/2016  X 1
181728 9 82 11/09/2016  X 

and Chris said...

Thanks for providing the test scripts... even if they need a little massaging ;)

My understanding of what you want is:

- Display a 1 on the first date in a run of consecutive attendances
- Consecutive dates exclude days the school was closed. So if someone attended on Friday and the following Monday, this is consecutive (because Weekend!)

If so, you can do it like so:

- Join the student and attendance tables together. For each student also return the previous attendance date. Use lag() to do this
- Separately return all the days school is open. Again, for each of these days, calculate the previous day it was open

You now have two tables of data. If, for a particular attendance, the previous attendance <> the previous day school is open this is a new sequence. So to get the output you've requested:

- Join the attendance data to the filtered calendar data on date
- Use case to see if the previous dates from the two data sets match

This gives you:

CREATE TABLE STUDENTS (
  ID                           NUMBER(10)     NOT NULL,
  schoolid                       number(10),
   STUDENT_NUMBER                 NUMBER(10)
)
/

insert into students values(1821,82,181535) ;
insert into students values(13892,82,180784);
insert into students values(12877,82,161314);
insert into students values(14088,82,181728);
/

CREATE TABLE ATTENDANCE (
  ID                   NUMBER(10) NOT NULL,
  CALENDAR_DAYID       NUMBER(10) ,
  ATTENDANCE_CODEID    NUMBER(10),  
  STUDENTID            NUMBER(10),  
  schoolid             number(10),
  ATT_MODE_CODE        VARCHAR2(20 CHAR),  
  ATT_DATE             DATE  
)
/
insert into attendance values ( 
23642330,254090,10126,1821,82,'ATT_ModeDaily',to_date('03-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23642335,254091,10126,1821,82,'ATT_ModeDaily',to_date('04-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23676557,254094,10126,1821,82,'ATT_ModeDaily',to_date('07-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23726471,254103,10126,1821,82,'ATT_ModeDaily',to_date('16-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23726457,254104,10126,1821,82,'ATT_ModeDaily',to_date('17-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23726461,254105,10126,1821,82,'ATT_ModeDaily',to_date('18-NOV-16', 'dd-MON-YY')); 
 insert into attendance values ( 
23707597,254102,10126,13892,82,'ATT_ModeDaily',to_date('15-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23710920,254103,10126,13892,82,'ATT_ModeDaily',to_date('16-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23726967,254104,10126,13892,82,'ATT_ModeDaily',to_date('17-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23726965,254105,10126,13892,82,'ATT_ModeDaily',to_date('18-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23766755,254108,10126,13892,82,'ATT_ModeDaily',to_date('21-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23766780,254109,10126,13892,82,'ATT_ModeDaily',to_date('22-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23766753,254110,10126,13892,82,'ATT_ModeDaily',to_date('23-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23810950,254115,10126,13892,82,'ATT_ModeDaily',to_date('28-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23829495,254116,10126,13892,82,'ATT_ModeDaily',to_date('29-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23759842,254105,10130,12877,82,'ATT_ModeDaily',to_date('18-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23818901,254115,10130,12877,82,'ATT_ModeDaily',to_date('28-NOV-16', 'dd-MON-YY')); 
 insert into attendance values ( 
23670409,254094,10130,14088,82,'ATT_ModeDaily',to_date('07-NOV-16', 'dd-MON-YY'));
 insert into attendance values ( 
23693514,254096,10130,14088,82,'ATT_ModeDaily',to_date('09-NOV-16', 'dd-MON-YY'));


 CREATE TABLE ATTENDANCE_CODE
( ID                       NUMBER(10) NOT NULL,
  SCHOOLID                 NUMBER(10), 
  ATT_CODE                 VARCHAR2(10 CHAR)  
)
/
INSERT INTO ATTENDANCE_CODE VALUES (10126,82,'S');
INSERT INTO ATTENDANCE_CODE VALUES (10130,82,'X');
/

CREATE TABLE CALENDAR_DAY
( ID                NUMBER(10) NOT NULL,
  SCHOOLID          NUMBER(10),
  DATE_VALUE        DATE,
  INSESSION         NUMBER(10)
)  
/
insert into calendar_day values (254088,82,to_date('01-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254089,82,to_date('02-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254090,82,to_date('03-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254091,82,to_date('04-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254092,82,to_date('05-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254093,82,to_date('06-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254094,82,to_date('07-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254095,82,to_date('08-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254096,82,to_date('09-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254097,82,to_date('10-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254098,82,to_date('11-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254099,82,to_date('12-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254100,82,to_date('13-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254101,82,to_date('14-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254102,82,to_date('15-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254103,82,to_date('16-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254104,82,to_date('17-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254105,82,to_date('18-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254106,82,to_date('19-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254107,82,to_date('20-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254108,82,to_date('21-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254109,82,to_date('22-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254110,82,to_date('23-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254111,82,to_date('24-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254112,82,to_date('25-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254113,82,to_date('26-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254114,82,to_date('27-NOV-16', 'dd-MON-YY'),0);
insert into calendar_day values (254115,82,to_date('28-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254116,82,to_date('29-NOV-16', 'dd-MON-YY'),1);
insert into calendar_day values (254117,82,to_date('30-NOV-16', 'dd-MON-YY'),1);

commit;

with att as (
select s.student_number, a.schoolid, a.att_date, ac.att_code,
       lag(att_date, 1, date'1900-01-01') over (partition by s.student_number order by att_date) prev
from   attendance a
join   students s
on     a.studentid = s.id
join   attendance_code ac
on     a.attendance_codeid = ac.id
and    a.schoolid = ac.schoolid
), dates as (
  select date_value, lag(date_value, 1, date'1900-01-01') over (order by date_value) prev_dt 
  from   calendar_day
  where  insession = 1
)
  select att.student_number, att.schoolid, att.att_date, att.att_code,
         case when prev != prev_dt then 1 end occ
  from   dates
  join   att
  on     att_date = date_value
  order  by student_number, att_date;

STUDENT_NUMBER  SCHOOLID  ATT_DATE     ATT_CODE  OCC  
161,314         82        18-NOV-2016  X         1    
161,314         82        28-NOV-2016  X         1    
180,784         82        15-NOV-2016  S         1    
180,784         82        16-NOV-2016  S              
180,784         82        17-NOV-2016  S              
180,784         82        18-NOV-2016  S              
180,784         82        21-NOV-2016  S              
180,784         82        22-NOV-2016  S              
180,784         82        23-NOV-2016  S              
180,784         82        28-NOV-2016  S              
180,784         82        29-NOV-2016  S              
181,535         82        03-NOV-2016  S         1    
181,535         82        04-NOV-2016  S              
181,535         82        07-NOV-2016  S              
181,535         82        16-NOV-2016  S         1    
181,535         82        17-NOV-2016  S              
181,535         82        18-NOV-2016  S              
181,728         82        07-NOV-2016  X         1    
181,728         82        09-NOV-2016  X 

Rating

  (3 ratings)

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

Comments

Perfect!

Venkat Sagaram, December 14, 2016 - 5:48 pm UTC

Thank you so very much!

Worked just the way I needed, I have made small changes to tweak my needs!!

Just perfect!!
Connor McDonald
December 15, 2016 - 2:11 am UTC

glad we could help

Calculate more than attendance period per day with some conditions

ahmed hassan, May 20, 2023 - 11:20 am UTC

I have the foloowing 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.

Chris Saxon
May 22, 2023 - 12:37 pm UTC

I'm unclear exactly how the logic works, I'm guessing that you want to combine chains of operation codes of the first number (1 always?) followed by chains of the next operation code (2 always?).

In which case pattern matching will help, looking something like this:

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 )
  );

Attendance clean-up task

mathguy, May 23, 2023 - 1:09 am UTC

Re: Ahmed Hassan's question from May 20, 2023:

First, I don't understand why you posted your question in an old thread; your question is not related to the thread's topic (even though both are about "attendance"). Why not ask your own question, in a new thread?

Second, if I understand your task correctly, it is possible that the first event for a given employee on a given date is a "leave" (operation code = 2), and/or the last event is an "entrance" (operation code = 1). And you want to show entrance - > leave pairs, in that order; if the first event on the day is a leave, show NULL for entrance, and if the last event is an entrance, show NULL for leave. Correct?

And, you want to do this for only one employee at a time. (If you need this done for all employees at once, the query can be modified easily.)

I will assume that OPERATION_CODE is constrained to NOT NULL and only two values, 1 or 2. If this is not the case, please clarify.

The solution is similar to what Chris posted. Here are the differences: First, since you want to limit the input data to a single employee and a single calendar date, the preliminary subquery called PREP in the WITH clause does this filtering. Then MATCH_RECOGNIZE applies to the result set from this first pass. The PATTERN and DEFINE clauses are different, to match your requirement.

My code in the PREP subquery is slightly different from yours, too. Whenever possible, compare something like OPERATION_DATE to some fixed values (or bind variables, or expressions involving only bind variables and constants) without wrapping OPERATION_DATE within functions like TO_CHAR. If you have an index on OPERATION_DATE, or perhaps on (EMP_CODE, OPERATION_DATE), the optimizer will be able to use it to quickly access just the required rows in the filtering phase, but only if OPERATION_DATE appears on its own, not wrapped within function calls.

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
);

  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 


Connor McDonald
May 23, 2023 - 4:55 am UTC

nice stuff

More to Explore

Analytics

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