This discussion is archived
6 Replies Latest reply: Oct 14, 2010 1:28 AM by 737905 RSS

Grouping Continuous Dates

520297 Newbie
Currently Being Moderated
My Table is -

<pre>
H_DAYS
18/01/1999
19/01/1999
20/01/1999
26/01/1999
29/01/1999
31/01/1999
01/02/1999
02/02/1999
</pre>
I need to group all the continuous dates like -

<pre>
From_Date To_Date Days
18/01/1999 20/01/1999 3
26/01/1999 26/01/1999 1
29/01/1999 29/01/1999 1
31/01/1999 02/02/1999 3
</pre>

Thanks in Advance
  • 1. Re: Grouping Continuous Dates
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    It is called sense of Tabibitosan B-)
    Re: Having trouble of thinking of a query to accomplish this
    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);
  • 2. Re: Grouping Continuous Dates
    520297 Newbie
    Currently Being Moderated
    Thankyou very much... its working great for my query
  • 3. Re: Grouping Continuous Dates
    364499 Newbie
    Currently Being Moderated
    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.

    Basically, i am trying to prepare employee absence history summary based on the following information:

    EMP_ID     WORK_DT     HRS     LNAME     FNAME     REASON     PAID

    123     01/06/2009     8.0     Abc     Xyz     Sick     Paid
    123     01/07/2009     8.0     Abc     Xyz     Sick     Paid
    123     01/08/2009     8.0     Abc     Xyz     Sick     Paid
    123     01/09/2009     8.0     Abc     Xyz     Sick     Paid
    123     01/16/2009     8.0     Abc     Xyz     FMLA EMP     Paid
    123     02/17/2009     8.0     Abc     Xyz     Sick     Paid
    123     02/18/2009     8.0     Abc     Xyz     Sick     Paid
    123     03/30/2009     8.0     Abc     Xyz     Jury Service Paid
    123     05/21/2009     4.0     Abc     Xyz     Sick     Paid
    123     05/22/2009     4.0     Abc     Xyz     Sick     Paid
    123     07/03/2009     8.0     Abc     Xyz     Sick     Paid
    123     08/25/2009     8.0     Abc     Xyz     FMLA EMP     Paid
    123     08/27/2009     4.5     Abc     Xyz     FMLA EMP     Paid
    123     09/21/2009     8.0     Abc     Xyz     Sick     Unpaid
    123     09/22/2009     8.0     Abc     Xyz     Sick     Unpaid


    Where consecutive days (entire day i.e. 8 hrs or more) absences should be calculated as 1 incident and should be shown together as a single record along with Days, Hours and incident number as follow. As it shows below, if any incident is with less than 8 hours then it should be considered as a new incident. i.e. two consecutive incidents on 5/21 and 5/22 should appear as shown below:

    Here is what I am trying to get:

    EMP_ID START_DT END_DT DAYS HOURS MINs INCIDENT REASON     PAID

    123 01/06/2009 01/09/2009     4 32     0     1     Sick     Paid
    123 01/16/2009 01/16/2009     1 8     0     2     FMLA EMP Paid
    123 02/17/2009 02/18/2009     2 16     0     3     Sick     Paid
    123 03/30/2009 03/30/2009     1 8     0     4     Jury Service Paid
    123 05/21/2009 05/21/2009     0 4     0     5     Sick     Paid
    123 05/22/2009 05/22/2009     0 4     0     6     Sick     Paid
    123 07/03/2009 07/03/2009     1 8     0     7     Sick     Paid
    123 08/25/2009 08/25/2009     1 8     0     8     FMLA EMP Paid
    123 08/27/2009 08/27/2009     0 4     30     9     FMLA EMP Paid
    123 09/21/2009 09/22/2009     2 16     0     10     Sick     Unpaid

    As it shows above I am able to group consecutive dates and list correct sum of the hours howerver it also groups together two consecutive 4 hours absents (5/21 and 5/22) and shows them as a single incident and sum it to 8 hours, which is incorrect.

    I will apreciate if you could give any tips to get it done.

    Thank you in advance.
  • 4. Re: Grouping Continuous Dates
    Solomon Yakobson Guru
    Currently Being Moderated
    sunIT wrote:
    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.
    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.

    SY.
  • 5. Re: Grouping Continuous Dates
    364499 Newbie
    Currently Being Moderated
    Hi Solomon, I apologize for putting my question in another post. in past i had an experience where i created new post and one of the group member replied that i should look for the similar existing post and then post the question there instead of creating a new duplicate thread for similar type of questions.. anyways, i am sorry again for that.

    I created new post under SQL and PL/SQL as "Group Consecutive dates to get absences (Days, hours, Mins) and incidents"

    Any suggestions or tips are most welcome.

    Thank you for your time and for correcting my mistake.
  • 6. Re: Grouping Continuous Dates
    737905 Expert
    Currently Being Moderated
    Also try this:
    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> 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points