Link to home
Create AccountLog in
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad ImranFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad Imran
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of Naveen Kumar
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
Avatar of Sean Stuber
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;
/

Open in new window

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
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

OK, thanks a lot
Good and Thanks
Right mr. nav looking for your expertise again regarding the same issue

details are here

 https://www.experts-exchange.com/questions/26711216/Finding-consective-absent-dates.html