Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Grouping Continuous Dates

520297Dec 20 2009 — edited Oct 14 2010
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
This post has been answered by Aketi Jyuuzou on Dec 20 2009
Jump to Answer

Comments

Aketi Jyuuzou
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);
Marked as Answer by 520297 · Sep 27 2020
2 people found this helpful
520297
Thankyou very much... its working great for my query
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.
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.
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.
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>
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 11 2010
Added on Dec 20 2009
#sql
6 comments
16,269 views