6 Replies Latest reply: Oct 14, 2010 3:28 AM by 737905 RSS

    Grouping Continuous Dates

    520297
      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
          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
            Thankyou very much... its working great for my query
            • 3. Re: Grouping Continuous Dates
              364499
              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
                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
                  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
                    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>