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

I thought that has solved my problem but i have problem with the start of new year.

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

(all the table and data is in comment posted <ID:34428390> of above question)

the function posted by nav was alright but today(Sunday 02 Jan 2011) the query is giving unexpected results


on 31-dec-2010 the query results
select sid
from (
select sid
from absent a
group by sid
)
where number_of_abdays(sid ) = 1
/
       SID
----------
        32
       363

and with date 01 jan or 02 jan it gives

SQL> /

       SID
----------
        32
       248
       363

but it should show 32 and 363 only as no_study_days table showing no working days from 25 dec 2010 to 01-jan-2011.

SQL> select * from no_study_days;

       IDX NDATE     OFF NDESC
---------- --------- --- ----------
         1 25-DEC-10 OFF OFF
         2 27-DEC-10 OFF
         3 28-DEC-10 OFF
         4 29-DEC-10 OFF
         5 30-DEC-10 OFF OFF
         6 31-DEC-10 OFF
         7 01-JAN-11 OFF

any idea what's wrong with this.

Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

will have a look at this but need to test this and get back to you
Avatar of Muhammad Ahmad Imran

ASKER

OK, was waiting for you.

thanks for help
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
ok that's good.

Could you explain how you check these on sql plus ( i see dbms_output.put_line in your code)

trying to understand the entire logic of code.

need your help though.

thanks in advance
that is just used for debugging to see what values are there in the arrays initially and after processing logic.

You can comment them even in the exception section and can be replaced to log/insert errors into log/debug table.

Did you test it to see if that works for you ?

Thanks
thanks again
Good and thanks.