Forum Stats

  • 3,839,935 Users
  • 2,262,549 Discussions
  • 7,901,101 Comments

Discussions

Categories

Grouping Continuous Dates

520297
520297 Member Posts: 25
edited Oct 14, 2010 4:28AM in SQL & PL/SQL
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
Tagged:
6eab7939-156f-4831-bb72-4942b8508992
· Share on TwitterShare on Facebook

Best Answer

Answers

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Answer ✓
    It is called sense of Tabibitosan B-)
    3959787
    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);
    6eab7939-156f-4831-bb72-4942b8508992User_MS55E
    · Share on TwitterShare on Facebook
  • 520297
    520297 Member Posts: 25
    Thankyou very much... its working great for my query
    · Share on TwitterShare on Facebook
  • 364499
    364499 Member Posts: 26
    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.
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,612 Red Diamond
    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.
    · Share on TwitterShare on Facebook
  • 364499
    364499 Member Posts: 26
    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.
    · Share on TwitterShare on Facebook
  • 737905
    737905 Member Posts: 804
    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>
    · Share on TwitterShare on Facebook
This discussion has been closed.