# Finding consective absent dates

It is a school absentee database, every morning after inserting today's absent student, the operator wants a list of those id's who are not coming since three consecutive (including today). There is a table NO_STUDY_DAYS holding public holidays. He wants to excludes those days either present in this table or a Sunday.

Suppose today is Monday, 27 Dec 2010 and 25 Dec 2010 is OFF (mind 26 Dec 2010 is already Sunday and it is not included in NO_STUDY_DAYS)
and absent table holds the following data (a sample of more than 1000 students,absent table might have millions)

1* select sid,absdate from absent where sid in (248,32,363) order by sid,absdate desc
SQL> /

SID ABSDATE
---------- ---------
32 24-DEC-10
32 23-DEC-10
32 22-DEC-10
32 21-DEC-10
32 20-DEC-10
32 18-DEC-10
32 15-DEC-10
32 14-DEC-10
32 13-DEC-10
248 24-DEC-10
248 23-DEC-10
248 21-DEC-10
248 20-DEC-10
248 19-DEC-10
248 18-DEC-10
363 24-DEC-10
363 23-DEC-10
363 22-DEC-10
363 10-DEC-10
363 09-DEC-10

20 rows selected.

SQL> select * from NO_STUDY_DAYS;

IDX NDATE     OFF_ NDESC
---------- --------- ---- ------------------------------
1 25-DEC-10 OFF  Public Holiday

The output should be 32 and 363 (in this current set of data) as these students are considered to be regular absent (their absentee dates are 24,23,22 respectively) but 248 has only two recent consecutive absdates.

The following query gives me the result until there is no Sunday or no date present NO_STUDY_DAYS
(how would I exclude sundays and dates present in NO_STUDY_DAYS table)

select sid from (
select sid, case when trunc(sysdate)-absdate in (0,1,2) then 1 else 0 end cs  from absent)
group by sid
having sum(cs) = 2

and suppose today(27-DEC-2010) id 248 is absent and 32 and 363 not then only 248 should return because the absent table look like this
An *sid* not present in absent table for a particular date means sid is present on that day
1* select sid,absdate from absent where sid in (248,32,363) order by sid,absdate desc
SQL> /

SID ABSDATE
---------- ---------
32 24-DEC-10
32 23-DEC-10
32 22-DEC-10
32 21-DEC-10
32 20-DEC-10
32 18-DEC-10
32 15-DEC-10
32 14-DEC-10
32 13-DEC-10
248 27-DEC-10
248 24-DEC-10
248 23-DEC-10
248 21-DEC-10
248 20-DEC-10
248 19-DEC-10
248 18-DEC-10
363 24-DEC-10
363 23-DEC-10
363 22-DEC-10
363 10-DEC-10
363 09-DEC-10
why not do it using a function ?
you want all students who have 3 continous leaves from the current date or even a student who has takes 3 days continously in the past.
This is a similar one but only difference is SAT/SUN are maintained in the holiday table but it is not in your case.

If you can maintain the SUNDAYS also along with the holidays in your no_study_days  table, then it becomes very simple and we can use the approach used in the  below one.

https://www.experts-exchange.com/questions/26629135/date-difference-excluding-Holidays.html

I want all students who have 3 continuous leaves from the current date only regardless they were present or absent before that.
including sudays in that table is useless because school will never open on sunday, so why put every sunday in this table.

Can you help
Sean Stuber

If today is the 27th and we're supposed to count today,  then no students have missed 3 consecutive days since nobody has missed today.

Your rules seem to be in conflict.  Despite the original question saying "including today" Do you REALLY mean we should NOT include the current day?
or, is your sample data not representative of the scenario and expected results you have described?

look at my first comment (ID:34427104), i said 248 is in the table

248  27-DEC-10

means he is absent today.

so if i check on friday 24th or saturday 25th or on sunday 26th then 32 and 363 should be returned but on monday 27th, only 248 should be returned.

i know that is hard to look at data but you can analysis only top 3 rows for each id (in orginal question and the in comment ID:34427104 )

thanks for helping out
there are many ways to do it and mine is just one way of doing it. first create the function and they try the below:

it works fine for me. In orer to test it, i have inserted one record into absent table for today's date for 32 sid as we are testing this today.

select *
from (
select sid
from absent a
group by sid
)
where number_of_abdays(sid ) = 1;

``````create or replace function number_of_abdays(x number) return number is

type tt is table of date index by binary_integer ;
type stt is table of number index by binary_integer;
mytab tt;
skip stt;

cc number:=0;
found number;

begin

for k in 0..9
loop
mytab(k) := trunc(sysdate) - k;
skip(k) := 0;

begin
select 1 into skip(k) from no_study_days where ndate = mytab(k);
exception
when no_data_found then
skip(k) := 0;
end;

if ( to_char(mytab(k),'DY' ) ='SUN') then
skip(k) := 1;
end if;
dbms_output.put_line('mytab('||k|| '):'|| mytab(k));
dbms_output.put_line('skip('||k|| '):'|| skip(k));
end loop;

for k in 0..9
loop

if skip(k) = 0 then
begin
select 1 into found from absent where absdate = mytab(k) and sid=x ;
exception
when no_data_found then
return 0;
end;

cc := cc+ 1;
exit when cc >=3;
end if;

end loop;
return 1;

exception when others then
dbms_output.put_line('in exception');
dbms_output.put_line('sqlcode:'|| sqlcode);
dbms_output.put_line('sqlerrm:'|| sqlerrm);
return 0;
end;
/
``````

here is my rewrite of my question

create table no_study_days (ndate date);

insert into no_study_days values ('25-dec-2010');

create table absent (sid number,absdate date,reason varchar2(2));

insert into absent values (32,'24-dec-2010','A');

insert into absent values (32,'23-dec-2010','A');

insert into absent values (32,'22-dec-2010','A');

insert into absent values (248,'24-dec-2010','A');

insert into absent values (248,'23-dec-2010','A');

insert into absent values (363,'24-dec-2010','A');

insert into absent values (363,'23-dec-2010','A');

insert into absent values (363,'22-dec-2010','A');

select * from absent order by sid, absdate desc;

SID ABSDATE   RE
---------- --------- --
32 24-DEC-10 A
32 23-DEC-10 A
32 22-DEC-10 A

248 24-DEC-10 A
248 23-DEC-10 A

363 24-DEC-10 A
363 23-DEC-10 A
363 22-DEC-10 A

8 rows selected.

i want to get those sid who have 3 continous absentees from today.

Suppose today is '24-dec-2010' (change my system date)

so most recent three schools days are 22 dec, 23 dec and 24 dec

select sid,sum(cs) from (
select sid,sysdate,absdate,trunc(sysdate) - absdate,
case when trunc(sysdate) - absdate in (1,2,0) then 1 else 0 end cs from absent order by sid,absdate desc)
group by sid
having sum(cs) = 3
/

SID    SUM(CS)
---------- ----------
32          3
363          3

But the problem begins with Sundays and NO_STUDY_DAYS table

insert into absent values (248,'27-dec-2010','A');

select * from absent order by sid, absdate desc;

SID ABSDATE   RE
---------- --------- --
32 24-DEC-10 A
32 23-DEC-10 A
32 22-DEC-10 A

248 27-DEC-10 A
248 24-DEC-10 A
248 23-DEC-10 A

363 24-DEC-10 A
363 23-DEC-10 A
363 22-DEC-10 A

9 rows selected.

and suppose today's date is 27-dec-2010. (change my system date again)

so most recent three schools days are 23 dec, 24 dec and 27 dec

(becuase 25 is off due to public holiday present in NO_STUDY_DAYS table and 26 dec is SUNDAY itself)

the above query results

*no rows selected*

but I was expecting 248 only (32 and 363 will be considered as present students if they are not in absent table on '27-dec-2010')

so expected result set for above data is

SID    SUM(CS)
---------- ----------
248          3

So how to achieve these results

Thanks a lot

@nav_kum_v

your function seems perfect at the moment but it takes some additional time to return results.

thanks
Naveen Kumar

membership
Create an account to see this answer
Signing up is free. No credit card required.

no big difference in speed though

can we remove every unwanted lines (may be like exception when other sections)

Cool function though, i much appreciate your precious time spending for me
no big difference in speed though  --> you might not be able to see the differenes but definitelly we have got rid of one extra loop which iterates for nine times right...

if you want to remove exceptions then we should get rid of the select statements itself and probably you can try to change the code by just selecting the data into new arrays instead of selects and then use them to compare accordingly in the code.

do not have enought time to do all this myself an test it...

Thanks