select min(H_DAYS),max(H_DAYS),count(*) from (select H_DAYS, H_DAYS - Row_Number() over(order by H_DAYS) as distance from t) group by distance order by min(H_DAYS);
sunIT wrote:And this is more complex case of grouping loosely grouped data - Tabibitosan (at least in form posted here) will not help you. Anyway, it is rude to post your question in someone else's posting. Create a separate post and I will give you a solution.
Hi Aketi, I am trying calculate absence history based on the absence_dt details and am able get it the way i wanted upto certain extent. however, not able to get exact output as required.
SQL> ed Wrote file afiedt.buf 1 with t as (select TO_DATE('18/01/1999','DD/MM/RRRR') dat from dual 2 union select TO_DATE('19/01/1999','DD/MM/RRRR') from dual 3 union select TO_DATE('20/01/1999','DD/MM/RRRR') from dual 4 union select TO_DATE('26/01/1999','DD/MM/RRRR') from dual 5 union select TO_DATE('29/01/1999','DD/MM/RRRR') from dual 6 union select TO_DATE('31/01/1999','DD/MM/RRRR') from dual 7 union select TO_DATE('01/02/1999','DD/MM/RRRR') from dual 8 union select TO_DATE('02/02/1999','DD/MM/RRRR') from dual) 9 SELECT MIN(dat),MAX(dat),MAX(dat) - MIN(dat) + 1 Days FROM 10 (select dat,dat - ROW_NUMBER() OVER(ORDER BY dat) rn 11 from t) 12* GROUP BY rn SQL> / MIN(DAT) MAX(DAT) DAYS --------- --------- ---------- 18-JAN-99 20-JAN-99 3 26-JAN-99 26-JAN-99 1 29-JAN-99 29-JAN-99 1 31-JAN-99 02-FEB-99 3 SQL>